Error : You tried to execute a query that does not include a specified expression 'AID' 1

[vba317]

The full error is :You tried to execute a query that does not include a specified expression 'AID' as part of an aggregate funtion.
I tried to change this to group by and that did not fix this. I am not even sure where to start on this type of error. Any help is appreciated.

INSERT INTO z_Calc_ICCFix ( uci, aid, encid, tranid, slid, billpd, DOS, PostDt, BillMonthSvc, ProvID, ProvName, LocumID, LocumName, RefProvID, RefProvName, POSID, POSName, FacID, FacName, DtRecvd, PrimInsSD, PrimInsName, TranType, CPTCode, CPTName, CPTComponent, RCID, RCName, BillMonthSvcLine, PostDtTrans, ServDtTrans, Amount )
SELECT "WMG" AS uci, imp_CS.AID, imp_CS.EID, imp_CS.TID, imp_CS.SLID, z_ImportMonthList.ID, imp_CS.[Dt Of Serv], imp_CS.[Post Dt], imp_CS.[Bill Month of Service], imp_CS.[Provider*], imp_CS.[Provider Name], imp_CS.[Locum Tenens*], imp_CS.[Locum Tenens Name], imp_CS.[Referring Phys Ptr*], imp_CS.[Referring Phys Name], imp_CS.[Place of Service*], imp_CS.[POS Name], imp_CS.[Facility*], imp_CS.[Facility Name], imp_CS.[Dt Received], imp_CS.[Prim Ins SD], imp_CS.[Prim Ins Name], imp_CS.[Trans Type], imp_CS.[CPT Code], imp_CS.[CPT Name], imp_CS.[CPT Component], imp_CS.[Revenue Center*], imp_CS.[Revenue Center Name], imp_CS.[Bill Month Of Serv Line], imp_CS.[Post Dt of Trans], imp_CS.[Serv Dt of Trans], Sum(-[Amount]) AS Amt
FROM imp_CS INNER JOIN z_ImportMonthList ON imp_CS.[Bill Mon of Trans] = z_ImportMonthList.MonPostID
WHERE (((imp_CS.[Trans Type])=3) AND ((imp_CS.[Credit Code Sd])="ICC"));

 

[Andrzejek]

Does the SELECT part of the query work OK?

Have fun.

---- Andy

 

[SkipVought]

hi,

Probably the message is totally unrealted to the actual error.

You do need a Group By like...

WHERE (((imp_CS.[Trans Type])=3) AND ((imp_CS.[Credit Code Sd])="ICC"))

Group By
  imp_CS.AID
, imp_CS.EID
, imp_CS.TID
, imp_CS.SLID
, z_ImportMonthList.ID
, imp_CS.[Dt Of Serv]
, imp_CS.[Post Dt]
, imp_CS.[Bill Month of Service]
, imp_CS.[Provider*]
, imp_CS.[Provider Name]
, imp_CS.[Locum Tenens*]
, imp_CS.[Locum Tenens Name]
, imp_CS.[Referring Phys Ptr*]
, imp_CS.[Referring Phys Name]
, imp_CS.[Place of Service*]
, imp_CS.[POS Name]
, imp_CS.[Facility*]
, imp_CS.[Facility Name]
, imp_CS.[Dt Received]
, imp_CS.[Prim Ins SD]
, imp_CS.[Prim Ins Name]
, imp_CS.[Trans Type]
, imp_CS.[CPT Code]
, imp_CS.[CPT Name]
, imp_CS.[CPT Component]
, imp_CS.[Revenue Center*]
, imp_CS.[Revenue Center Name]
, imp_CS.[Bill Month Of Serv Line]
, imp_CS.[Post Dt of Trans]
, imp_CS.[Serv Dt of Trans]
;

Also , Sum(-[Amount]) AS Amt [Amount] is unquallified with respect to a table, if [Amount] appears in both tables, then that's a problem, but I'd quallify it anyhow.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I took the - out of the amount and everything worked great! Thanks Skip