Count & SUM (Access 2000)

Topic

New Reply

Up front

Reply | Quote

Replies

For the “Total” change the “Group By” to either First if you want to see your criteria or Where if your don’t need to see the data. If you use the Where, you’ll have to uncheck the “Show” option

Reply | Quote

Thanks for the fast response! I tried a number of tests using ‘First’ but could not get the query to give me the ‘Count’ and ‘$Amount’ totals I need.

I think I might need to build a number of separate queries (one of each of my sort collections) and then create a report that will do the Counting and $Amount totaling… Just an idea that I am starting to investigate.

+==+++
Well, that doesn’t work very well. I created a bunch of queries but ,when I try to base the the report on more than one, I get an error because I am attaching the same $Amount field from each query. I can create a report to do my Count and Total Amount for one query, but that means I need to create a bunch of reports and this just doesn’t seem reasonable. Back to the drawing board…

+++====+++++=====++++

OK. For the next newbie to trip over this….
I was including the Name field so that I could verify the records being pulled. This field is not one of the sort fields so I ignored them except to set them to Not Showing. Well, just Not Showing is not enough to prevent them forcing all the records to show (with the bum count of 1 and the individual $Amount showing). Once I changed the ‘Total’ setting for these fields that I had been ignoring, to either First or Where (as suggested above by MBarron), I got the results I wanted (any setting except ‘Group by’ will work – not just First or Where).

So, ALL fields that are used for selection criteria must be set to ‘Where’; $Amount must be set to SUM; and either set a Field like ‘name’ to ‘Count’ or set ‘name’ to any setting except ‘Group by’. If you do not use ‘name’ for your counter, then use $Amount a second time – and set it to ‘Count’ in that second instance.

Enjoy!

Reply | Quote

It’s best only to include the fields you actually need in a totals query, and to leave out all others.

Fields that you want to group on (i.e. you want to return unique combinations of the field values) should be set to Group By.
Fields that you want to aggregate (i.e. you want to return the sum, minimum, count, … of the field values) should be set to the appropriate aggregate function – Sum, Min, Count etc.
Fields that you only want to use to fitler the records (i.e. to specify criteria) should be set to Where (which will automatically turn off the Show check box)
Other fields should be omitted from the query.

Reply | Quote