Totals and Percentages

[matpj]

I have a list box of problem types in my form.
I need to be able to add up the number of each problem-type, perform a grand total and work out the percentage of each problem-type of the grand total!


Can this be done within Access? or do I have to analyse the data in Excel?
I have tried doing it in Excel and recording a macro so this report can be run off whenever required, but because of sub-totalling etc, a macro seems to be harder to impement than I first realised.
Any ideas?

 

[gwinn7]

If I wanted to do accomplish something like your situation in Access (which is easy by the way!), I would either use a DAO recordset to loop through each record and sum the appropriate fields to private variables (and present their results on the form!) OR set the 'Controlsource' property of unbound Textboxes to the DCOUNT function. For example...

TextBox 1: "Total of YourType1:"
.ControlSource = DCOUNT(&quot;AnyColumn&quot;, &quot;YourTable&quot;, &quot;Type = '&quot; & YourType1 & &quot;'&quot <--- assuming your type is a string!

TextBox 2: &quot;Total of YourType2:&quot;
.ControlSource = DCOUNT(&quot;AnyColumn&quot;, &quot;YourTable&quot;, &quot;Type = '&quot; & YourType2 & &quot;'&quot <--- assuming your type is a string!

etc...

Note: If your type is not a string, but a number, leave out the single quotations in the conditional clause.

You can apply further discrimination of records by appending the conditional clause in the DCOUNT function by using AND and OR operators. Likewise, if you need to sum values based on a specific type, I would use the similar function called, &quot;DSUM&quot;.

To perform your percentage calculations, you should also be able to use the ControlSource property of more defined textboxes with your calculation. Or, you can always use the Form_Current event to set your textboxes with those percentage calcs.

I am sure there are a couple of other ways to do this. I can already think of one other. I hope this helps you.

Gary
gwinn7