Results 1 to 6 of 6
  1. #1
    beegee is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2016
    Posts
    12

    Sum/ grouping help

    Hi all,


    I have a list of almost 1 million rows of data that contain a charge for every visit a patient had to my hospital. How do I group the costs together to get a total cost for each patient? If the list was smaller then I could dump it in excel and pivot on the patients and sum the total costs. But what is the equivalent of doing that in access?

    Thank you in advance for any help you can provide!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    use a group by query.

    1. In the query designer select your table

    2. on the ribbon, click on the epsilon (totals)

    3. on the query grid, select PatientName - it should automatically fill in the total row with Group By

    4. now select cost, and in the total row, select sum from the dropdown list

    5. run the query

    Note: with a table of this size, indexing is important. So if PatientName (or whatever field you are using) is not indexed, index it, otherwise performance will be quite slow (about the same as Excel with 1m rows)

  3. #3
    beegee is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2016
    Posts
    12
    Thank you, Ajax! I will try this as soon as I learn how to index... In the meantime, I added "top 2000" to my SQL so I can play around with what you described as well as figuring out how to index in a table that is more manageable. Is that a good idea? It seems like if I can make it do what you described on a smaller scale (which I can then check against in excel), then it should work on the full list. Or am I going down a bad path?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    TOP 2000 - the query is very simple, takes a few seconds to create, so up to you.

    Indexing is also very easy, go into table design, select the field then select the indexing option Indexed - duplicates OK.

    Don't overdo the indexing - fields that should be indexed are those that are regularly sorted or filtered on, and don't bother indexing fields with lots of nulls or very few options (like yes/no fields). See post #6 of this link for a fuller explanation

    https://www.accessforums.net/showthread.php?t=56724

  5. #5
    beegee is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2016
    Posts
    12
    Even without the indexing, it worked!!!! Thank you again, Ajax!

  6. #6
    beegee is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2016
    Posts
    12
    I will definitely practice the indexing and I will read the post. Thank you again and happy holidays! (now, how do I mark this "solved" and give you a high/positive rating?)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Grouping
    By joeromero87 in forum Reports
    Replies: 2
    Last Post: 03-11-2015, 07:30 PM
  2. Grouping
    By McArthurGDM in forum Queries
    Replies: 3
    Last Post: 08-11-2014, 03:56 PM
  3. Help with grouping
    By RachelBedi in forum Queries
    Replies: 1
    Last Post: 11-05-2012, 12:55 PM
  4. Grouping
    By EricF in forum Reports
    Replies: 5
    Last Post: 07-18-2011, 03:58 PM
  5. Grouping
    By dref in forum Reports
    Replies: 1
    Last Post: 01-16-2010, 08:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums