Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

access query wont run.

Acces gives an error saying a field could refer to more than one table but they are all fully referenced i think so i cannot see why it fails:
q1uestions: [AbiCurrentcodes].[EngineTypediff]
i am trying to capture change events where the field enginetypediff has changed since last month.

SELECT QryClientDifferencesLogic.[abiCode], [AbiCurrentcodes].[EngineTypediff] AS CurrentT, [AbicodesPrevious].[EngineTypediff] AS PrevT, "2012-08" AS Expr1
FROM (QryClientDifferencesLogic LEFT JOIN AbicodesPrevious ON QryClientDifferencesLogic.abiCode = AbicodesPrevious.abiCode) LEFT JOIN Abicurrentcodes ON QryClientDifferencesLogic.abiCode = Abicurrentcodes.abiCode
WHERE (((QryClientDifferencesLogic.EngineTypediff)=-1));

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try referencing each field with its table in all occurances.
Also put table names in brackets in all instances

SELECT [QryClientDifferencesLogic].[abiCode], [AbiCurrentcodes].[EngineTypediff] AS CurrentT, [AbicodesPrevious].[EngineTypediff] AS PrevT, "2012-08" AS Expr1
FROM (QryClientDifferencesLogic LEFT JOIN AbicodesPrevious ON [QryClientDifferencesLogic].[abiCode] = [AbicodesPrevious].[abiCode]) LEFT JOIN Abicurrentcodes ON [QryClientDifferencesLogic].[abiCode] = [Abicurrentcodes].[abiCode]
WHERE ((([QryClientDifferencesLogic].[EngineTypediff])=-1));