将多个记录与1个ID组合成Access中的1个记录

Question

我有以下记录集合。

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 

理想的情况下我会做的是结合这使我得到1 LGH司机1 LGH号和所有与事件时间的总和在一行上列出的公司。我希望有一个相对简单的解决方案，我忽略了，但任何解决方案都会在这一点上做。任何想法或意见将不胜感激。谢谢。

Answer 1

如果每个lgh_number只是一个公司的记录，你可以使用SQL这样的：

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; 

最大聚合函数将为每个公司列返回不为空值