CODEWITHSUNDEEP
ms-accessms-access-2010
Tom
Tom

Reputation: 301

Combine several records with 1 id into 1 record in Access

I have the following collection of records.

lgh_driver1 | lgh_number | Origin Company ID | First Pickup Company ID | Second Pickup Company ID | First Drop Company ID | Second Drop Company ID | Ending Company ID | Event Time
XXXXXX       111111        XXXXWAR                                                                                                                                       15
XXXXXX       111111                            GORMC                                                                                                                     199
XXXXXX       111111                                                      ERGCON                                                                                          944
XXXXXX       111111                                                                                  QUASAL                                                              45
XXXXXX       111111                                                                                                          QUASAL                                      45
XXXXXX       111111                                                                                                                                   XXXXWAR            15

Ideally what I would like to do is combine these so that i get 1 lgh driver 1 lgh number and all of the companies listed on one line with a sum of the event times. I am hoping that there is a relatively simple solution that I am overlooking but any solution will do at this point. Any thoughts or comments would be greatly appreciated. Thank you.

Upvotes: 0

Views: 46

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

If you have just one Company record per lgh_number, you can use SQL like this:

SELECT Table1.lgh_driver1, Table1.lgh_number, Max(Table1.[Origin Company ID]) AS [MaxOfOrigin Company ID], Max(Table1.[First Pickup Company ID]) AS [MaxOfFirst Pickup Company ID], Max(Table1.[Second Pickup Company ID]) AS [MaxOfSecond Pickup Company ID], Max(Table1.[First Drop Company ID]) AS [MaxOfFirst Drop Company ID], Max(Table1.[Second Drop Company ID]) AS [MaxOfSecond Drop Company ID]
FROM Table1
GROUP BY Table1.lgh_driver1, Table1.lgh_number;

Max aggregate function will return not null value for each Company column

Upvotes: 1

Related Questions