将查询访问 2007 组更新为一行

Question

我正在玩访问和vba。我正在努力使用以下查询更新和分组 tblLoadListItems 中的行到 tblLoadSummary 中

 UPDATE tblLoadSummary INNER JOIN tblLoadingListItems ON (tblLoadSummary.salesno = tblLoadingListItems.salesno) AND (tblLoadSummary.loadnolink = tblLoadingListItems.loadnolink) SET tblLoadSummary.[Cust O/N] = [tblLoadingListItems].[Cust O/N], tblLoadSummary.traderid = [tblLoadingListItems].[traderid], tblLoadSummary.street = [tblLoadingListItems].[street], tblLoadSummary.[zone] = [tblLoadingListItems].[zone], tblLoadSummary.salesno = [tblLoadingListItems].[salesno], tblLoadSummary.[Customer Ref] = [tblLoadingListItems].[Customer Ref], tblLoadSummary.DeliveryAddress = [tblLoadingListItems].[DeliveryAddress], tblLoadSummary.Postcode = [tblLoadingListItems].[Postcode], tblLoadSummary.SumOfnoofpacks = Sum([tblLoadingListItems].[packs1]), tblLoadSummary.SumOfnoofboxes = Sum([tblLoadingListItems].[noofboxes]), tblLoadSummary.contact = [tblLoadingListItems].[contact], tblLoadSummary.telephone = [tblLoadingListItems].[telephone], tblLoadSummary.fax = [tblLoadingListItems].[fax], tblLoadSummary.email = 
[tblLoadingListItems].[email], tblLoadSummary.deliverycontact = [tblLoadingListItems].[deliverycontact], tblLoadSummary.deliverytelephone = [tblLoadingListItems].[deliverytelephone], tblLoadSummary.deliveryfax = [tblLoadingListItems].[deliveryfax], tblLoadSummary.deliveryemail = [tblLoadingListItems].[deliveryemail], tblLoadSummary.acknowaddress = [tblLoadingListItems].[acknowaddress]
 WHERE (((tblLoadSummary.salesno)="SM-100118") AND ((tblLoadSummary.loadnolink)=32232))

如果我显示数据表视图，我会返回两行，我试图将这些行分组为一行。当我尝试在 WHERE 之后添加 GROUP BY 时，我收到语法错误，缺少所有字段的运算符。

你能给我指出正确的方向吗？

Answer 1

我不确定访问权限，但像这样开始更新，也许是错误的：

Update tblLoadSummary inner join...

像这样尝试

 update t set .... from tblLoadSummary t inner join tblLoadSummary s on ...

这是完整查询

UPDATE t-- tblLoadSummary 
   SET t.CustO/N = s.CustO/N,
       t.traderid          = s.traderid,
       t.street            = s.street,
       t.zone = s.zone,
       t.salesno           = s.salesno,
       t.CustomerRef = s.Customer Ref,
       t.DeliveryAddress   = s.DeliveryAddress,
       t.Postcode          = s.Postcode,
       t.SumOfnoofpacks    = Sum(s.packs1),
       t.SumOfnoofboxes    = Sum(s.noofboxes),
       t.contact           = s.contact,
       t.telephone         = s.telephone,
       t.fax               = s.fax,
       t.email             = s.email,
       t.deliverycontact   = s.deliverycontact,
       t.deliverytelephone = s.deliverytelephone,
       t.deliveryfax       = s.deliveryfax,
       t.deliveryemail     = s.deliveryemail,
       t.acknowaddress     = s.acknowaddress
from tblLoadSummary as t
 INNER JOIN tblLoadingListItems as s ON 
 (t.salesno = s.salesno) AND (t.loadnolink = s.loadnolink)
 WHERE (((t.salesno) = "SM-100118") AND
       ((t.loadnolink) = 32232))