Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    Edison, New Jersey, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count & SUM (Access 2000)

    Up front

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count & SUM (Access 2000)

    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
    Attached Images Attached Images

  3. #3
    Star Lounger
    Join Date
    Jul 2001
    Location
    Edison, New Jersey, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count & SUM (Access 2000)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count & SUM (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •