Grouping Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003. i would like to group records based on a reference number, the
only difference in the records being the transaction date and the cost which
will be added up. I would like to be able to use the first date that the
reference number refers to. Reference number is travel booking and
subsequent entries are modification to booking and additional costs incurred.
eg.
Ref No Date Cost
123 10-01-05 50
123 12-01-05 23
123 15-01-05 10
Result i would like is
123 10-01-05 83
it needs to be a fairly simple solution as 10,000's of records to group and
would like it to happen quickly!
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Ref No],
Min([Your Table].[Date]) AS [Original Booking Date],
Sum([Your Table].[Cost]) AS [Total Cost]
FROM
[Your Table]
GROUP BY
[Your Table].[Ref No]

This assumes your table is named "Your Table" and that the data type of the
"Date" field is Date/Time.
 
Try sorting the records on the reference number, with the reference number
appearing to left of all other fields in the query. I would then sort by
date.

The query results would then be listed by date but all records with the
reference number would be grouped.
 
Back
Top