Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    group records based on a field (Access XP)

    I would like to do a query/run a report on our database that groups transaction records based on the customer ID number. I would like all records to be summarized into one line per ID number and have the payment amounts (in the PAYMENT_AMOUNT field) shown as the sum total of all the amounts per ID. I can get to the point where I have all the records for each ID sorted by ID but I can't group them to show in one line with a sum total. Can someone please help?
    (I've attached a tiny snipit of the database I'm working with)

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

    Re: group records based on a field (Access XP)

    Possibility 1:
    - Create a query based on your table.
    - Add the ID and Payment_Amount fields to the query grid.
    - Select View | Totals.
    - Change the Total option for Payment_Amount to Sum.
    - Switch to datasheet view.
    - You can create a report based on this query.

    Possibility 2:
    - Use the Wizard to create a report based on your table.
    - Add the ID and Payment_Amount fields.
    - Group on ID.
    - Specify Totals options.
    - Select Sum, and specify that you only want totals, no details.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: group records based on a field (Access XP)

    Thank you very much!

Posting Permissions

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