Solved

union query says field could refer to more than one table

Posted on 2011-03-15
1
377 Views
Last Modified: 2012-05-11
I have to access queries that work perfectly but need a union of the two, I am working in access 2010.

The field [mvris code] is the culprit but i cannot see where the problem lay i have included the two queries to union.


SELECT [INTRO DATE], [Previous Intro Date], MARQUE, [Previous Marque], [MODEL RANGE], [Previous Model Range], [RANGE SERIES], [Previous Range Series], VARIANT, [Previous Variant], CC, [Previous CC], [NOM CC], [Previous Nom CC], DOORS, [Previous DOORS], [BODY TYPE], [Previous BODY TYPE], TRANSMISSION, [Previous TRANSMISSION], FUEL, [Previous FUEL], ASPIRATION, [Previous ASPIRATION], [DRIVE TYPE], [Previous DRIVE TYPE], [DRIVING AXLE], [Previous DRIVING AXLE], [FORWARD GEARS], [Previous FORWARD GEARS], SEATS, [Previous SEATS], [ENGINE MODEL], [Previous ENGINE MODEL], [NO CYLINDERS], [NO CYLINDERS], [Previous NO CYLINDERS], [VALVES PER CYLINDER], [Previous VALVES PER CYLINDER], [VALVE GEAR], [Previous VALVE GEAR], [POWER KW], [Previous POWER KW], [CALC BHP], [Previous CALC BHP], WHEELBASE, [Previous WHEELBASE], GVW, [Previous GVW], [CAB TYPE], [Previous CAB TYPE], [VAN ROOF CONFIG], [Previous VAN ROOF CONFIG], [WHEELBASE TYPE], [Previous WHEELBASE TYPE], CalcNomCC, [Previous CalcNomCC], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff, [MVRIS CODE], [variantDiff], [AbiMatchedStr2], [AbiMatchedStr], [CapMatchedStr2], [CapMatchedStr], [ContinentalMatchedStr], [ContinentalMatchedStr2], [GlassMatchedStr], [GlassMatchedStr2], [HalfordMatchedStr], [HalfordMatchedStr2], [IDSMatchedStr], [IDSMatchedStr2], [MichelinMatchedStr], [MichelinMatchedStr2], [TechDocMatchedStr], [TechDocMatchedStr2], [VividMatchedStr], [VividMatchedStr2],  [SMMTComparitorDate]
from [qrycomparechanges]
UNION SELECT [INTRO DATE], [Previous Intro Date], MARQUE, [Previous Marque], [MODEL RANGE], [Previous Model Range], [RANGE SERIES], [Previous Range Series], VARIANT, [Previous Variant], CC, [Previous CC], [NOM CC], [Previous Nom CC], DOORS, [Previous DOORS], [BODY TYPE], [Previous BODY TYPE], TRANSMISSION, [Previous TRANSMISSION], FUEL, [Previous FUEL], ASPIRATION, [Previous ASPIRATION], [DRIVE TYPE], [Previous DRIVE TYPE], [DRIVING AXLE], [Previous DRIVING AXLE], [FORWARD GEARS], [Previous FORWARD GEARS], SEATS, [Previous SEATS], [ENGINE MODEL], [Previous ENGINE MODEL], [NO CYLINDERS], [NO CYLINDERS], [Previous NO CYLINDERS], [VALVES PER CYLINDER], [Previous VALVES PER CYLINDER], [VALVE GEAR], [Previous VALVE GEAR], [POWER KW], [Previous POWER KW], [CALC BHP], [Previous CALC BHP], WHEELBASE, [Previous WHEELBASE], GVW, [Previous GVW], [CAB TYPE], [Previous CAB TYPE], [VAN ROOF CONFIG], [Previous VAN ROOF CONFIG], [WHEELBASE TYPE], [Previous WHEELBASE TYPE], CalcNomCC, [Previous CalcNomCC], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff, [MVRIS CODE], [variantDiff],  [AbiMatchedStr2], [AbiMatchedStr], [CapMatchedStr2], [CapMatchedStr], [ContinentalMatchedStr], [ContinentalMatchedStr2], [GlassMatchedStr], [GlassMatchedStr2], [HalfordMatchedStr], [HalfordMatchedStr2], [IDSMatchedStr], [IDSMatchedStr2], [MichelinMatchedStr], [MichelinMatchedStr2], [TechDocMatchedStr], [TechDocMatchedStr2], [VividMatchedStr], [VividMatchedStr2],  [SMMTComparitorDate]
from [qrycomparechangesAllTech];

'******************************************
qrycomparechanges

SELECT SMMT.[INTRO DATE], SMMT_Previous.[INTRO DATE] AS [Previous Intro Date], SMMT.MARQUE, SMMT_Previous.MARQUE AS [Previous Marque], SMMT.[MODEL RANGE], SMMT_Previous.[MODEL RANGE] AS [Previous Model Range], SMMT.[RANGE SERIES], SMMT_Previous.[RANGE SERIES] AS [Previous Range Series], SMMT.VARIANT, SMMT_Previous.VARIANT AS [Previous Variant], SMMT.CC, SMMT_Previous.CC AS [Previous CC], SMMT.[NOM CC], SMMT_Previous.[NOM CC] AS [Previous Nom CC], SMMT.DOORS, SMMT_Previous.DOORS AS [Previous DOORS], SMMT.[BODY TYPE], SMMT_Previous.[BODY TYPE] AS [Previous BODY TYPE], SMMT.TRANSMISSION, SMMT_Previous.TRANSMISSION AS [Previous TRANSMISSION], SMMT.FUEL, SMMT_Previous.FUEL AS [Previous FUEL], SMMT.ASPIRATION, SMMT_Previous.ASPIRATION AS [Previous ASPIRATION], SMMT.[DRIVE TYPE], SMMT_Previous.[DRIVE TYPE] AS [Previous DRIVE TYPE], SMMT.[DRIVING AXLE], SMMT_Previous.[DRIVING AXLE] AS [Previous DRIVING AXLE], SMMT.[FORWARD GEARS] AS [FORWARD GEARS], SMMT_Previous.[FORWARD GEARS] AS [Previous FORWARD GEARS], SMMT.SEATS, SMMT_Previous.SEATS AS [Previous SEATS], SMMT_Previous.[ENGINE MODEL] AS [Previous ENGINE MODEL], SMMT.[NO CYLINDERS], SMMT_Previous.[NO CYLINDERS] AS [Previous NO CYLINDERS], SMMT.[VALVES PER CYLINDER] AS [VALVES PER CYLINDER], SMMT_Previous.[VALVES PER CYLINDER] AS [Previous VALVES PER CYLINDER], SMMT.[VALVE GEAR] AS [VALVE GEAR], SMMT_Previous.[VALVE GEAR] AS [Previous VALVE GEAR], SMMT.[POWER KW], SMMT_Previous.[POWER KW] AS [Previous POWER KW], SMMT.[CALC BHP], SMMT_Previous.[CALC BHP] AS [Previous CALC BHP], SMMT.WHEELBASE, SMMT_Previous.WHEELBASE AS [Previous WHEELBASE], SMMT.GVW, SMMT_Previous.GVW AS [Previous GVW], SMMT.[CAB TYPE], SMMT_Previous.[CAB TYPE] AS [Previous CAB TYPE], SMMT.[VAN ROOF CONFIG], SMMT_Previous.[VAN ROOF CONFIG] AS [Previous VAN ROOF CONFIG], SMMT.[WHEELBASE TYPE], SMMT_Previous.[WHEELBASE TYPE] AS [Previous WHEELBASE TYPE], SMMT.CalcNomCC, SMMT_Previous.CalcNomCC AS [Previous CalcNomCC], IIf([smmt_previous].[variant]=MakeEqualStr([smmt].[variant],[smmt_previous].[variant]),0,1) AS VariantChange, MakeEqualStr([smmt].[variant],[smmt_previous].[variant]) AS MakeEqual, IIf(IsNull([smmt].[variant]) Or IsNull([smmt_previous].[variant]),-1,matchstrings([smmt].[variant],[smmt_previous].[variant])) AS WordsMatched, QrySMMTDifferencesLogic.Introdiff, QrySMMTDifferencesLogic.MarqueDiff, QrySMMTDifferencesLogic.ModelRangeDiff, QrySMMTDifferencesLogic.RangeSeriesDiff, QrySMMTDifferencesLogic.CCDiff, QrySMMTDifferencesLogic.NomCCDiff, QrySMMTDifferencesLogic.DoorsDiff, QrySMMTDifferencesLogic.BodyTypeDiff, QrySMMTDifferencesLogic.TransmissionDiff, QrySMMTDifferencesLogic.FuelDiff, QrySMMTDifferencesLogic.AspirationDiff, QrySMMTDifferencesLogic.DriveTypeDiff, QrySMMTDifferencesLogic.DrivingAxleDiff, QrySMMTDifferencesLogic.ForwardGearsDiff, QrySMMTDifferencesLogic.SeatsDiff, QrySMMTDifferencesLogic.EngineModelDiff, QrySMMTDifferencesLogic.NoCylDiff, QrySMMTDifferencesLogic.ValvespercylDiff, QrySMMTDifferencesLogic.valvegearDiff, QrySMMTDifferencesLogic.powerkwDiff, QrySMMTDifferencesLogic.calcbhpDiff, QrySMMTDifferencesLogic.gvwDiff, QrySMMTDifferencesLogic.cabDiff, QrySMMTDifferencesLogic.vanroofDiff, QrySMMTDifferencesLogic.wheelbasetypeDiff, QrySMMTDifferencesLogic.[MVRIS CODE], SMMT.[ENGINE MODEL], 1 AS VariantDiff, IIf(IsNull([QryAbiCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryAbiCWCodeInMatchTableWithNoMatch]![AbiMatched]=True,"CWCode in match table with no client code","Matched")) AS AbiMatchedStr2, IIf(IsNull([QryAbiCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryAbiCWCodeInMatchTableWithNoMatch]![AbiMatched]=True,False,True)) AS AbiMatchedStr, QryAbiCWCodeInMatchTableWithNoMatch.AbiMatched, IIf(IsNull([QryCapCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"Not Matched",IIf([QryCapCWCodeInMatchTableWithNoMatch]![CapMatched]=True,"CWCode in match table with no client code","Matched")) AS CapMatchedStr2, IIf(IsNull([QryCapCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryCapCWCodeInMatchTableWithNoMatch]![CapMatched]=True,False,True)) AS CapMatchedStr, IIf(IsNull([QryContinentalCWCodeInMatchTableWithNoMatch]![cwCODE]),False,IIf([QryContinentalCWCodeInMatchTableWithNoMatch]![ContMatched]=0,False,True)) AS ContinentalMatchedStr, IIf(IsNull([QryContinentalCWCodeInMatchTableWithNoMatch]![cwCODE]),"Not Matched",IIf([QryContinentalCWCodeInMatchTableWithNoMatch]![ContMatched]=0,"CWCode in match table with no client code","Matched")) AS ContinentalMatchedStr2, IIf(IsNull([QryGlassesCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryGlassesCWCodeInMatchTableWithNoMatch]![GlassMatched]=True,False,True)) AS GlassMatchedStr, IIf(IsNull([QryGlassesCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryGlassesCWCodeInMatchTableWithNoMatch]![GlassMatched]=True,"CWCode in match table with no client code","Matched")) AS GlassMatchedStr2, IIf(IsNull([QryhalfordsCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryHalfordsCWCodeInMatchTableWithNoMatch]![HalfordMatched]=True,False,True)) AS HalfordMatchedStr, IIf(IsNull([QryhalfordsCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"NotMatched",IIf([QryHalfordsCWCodeInMatchTableWithNoMatch]![halfordMatched]=True,"CWCode in match table with no client code","Matched")) AS HalfordMatchedStr2, IIf(IsNull([QryIDSCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryIDSCWCodeInMatchTableWithNoMatch]![IDSMatched]=True,False,True)) AS IDSMatchedStr, IIf(IsNull([QryIDSCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"Not Matched",IIf([QryIDSCWCodeInMatchTableWithNoMatch]![IDSMatched]=True,"CWCode in match table with no client code","Matched")) AS IDSMatchedStr2, IIf(IsNull([QryMichelinCWCodeInMatchTableWithNoMatch]![CWCODE]),False,IIf([QryMichelinCWCodeInMatchTableWithNoMatch]![MichelinMatched]=0,False,True)) AS MichelinMatchedStr, IIf(IsNull([QryMichelinCWCodeInMatchTableWithNoMatch]![CWCODE]),"Not Matched",IIf([QryMichelinCWCodeInMatchTableWithNoMatch]![MichelinMatched]=0,"CWCode in match table with no client code","Matched")) AS MichelinMatchedStr2, IIf(IsNull([QryTechdocCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryTechDocCWCodeInMatchTableWithNoMatch]![TechDocMatched]=True,False,True)) AS TechDocMatchedStr, IIf(IsNull([QryTechDocCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryTechDocCWCodeInMatchTableWithNoMatch]![TechDocMatched]=True,"CWCode in match table with no client code","Matched")) AS TechDocMatchedStr2, IIf(IsNull([QryvividCWCodeInMatchTableWithNoMatch]![MvrisCODE]),False,IIf([QryvividCWCodeInMatchTableWithNoMatch]![vividMatched]=True,False,True)) AS VividMatchedStr, IIf(IsNull([QryvividCWCodeInMatchTableWithNoMatch]![mvrisCODE]),"Not Matched",IIf([QryvividCWCodeInMatchTableWithNoMatch]![vividMatched]=True,"CWCode in match table with no client code","Matched")) AS VividMatchedStr2, Format(Now(),"dd/mm/yyyy") AS SMMTComparitorDate
FROM ((((((((((SMMT INNER JOIN QrySMMTDifferencesLogic ON SMMT.[MVRIS CODE] = QrySMMTDifferencesLogic.[MVRIS CODE]) INNER JOIN SMMT_Previous ON QrySMMTDifferencesLogic.[MVRIS CODE] = SMMT_Previous.[MVRIS CODE]) LEFT JOIN QryAbiCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryAbiCWCodeInMatchTableWithNoMatch.[MVRIS CODE]) LEFT JOIN QryCapCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryCapCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryContinentalCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryContinentalCWCodeInMatchTableWithNoMatch.CWCode) LEFT JOIN QryGlassesCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryGlassesCWCodeInMatchTableWithNoMatch.[MVRIS CODE]) LEFT JOIN QryVividCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryVividCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryTechdocCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryTechdocCWCodeInMatchTableWithNoMatch.[Mvris Code]) LEFT JOIN QryMichelinCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryMichelinCWCodeInMatchTableWithNoMatch.CWCode) LEFT JOIN QryIDSCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryIDSCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryHalfordsCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogic.[MVRIS CODE] = QryHalfordsCWCodeInMatchTableWithNoMatch.MvrisCode
WHERE (((IIf(smmt_previous.variant=MakeEqualStr(smmt.variant,smmt_previous.variant),0,1))>0) And ((IIf(IsNull(smmt.variant) Or IsNull(smmt_previous.variant),-1,matchstrings(smmt.variant,smmt_previous.variant)))=0));

'********************************************************
'qrycomparechangestech
SELECT SMMT.[INTRO DATE], SMMT_Previous.[INTRO DATE] AS [Previous Intro Date], SMMT.MARQUE, SMMT_Previous.MARQUE AS [Previous Marque], SMMT.[MODEL RANGE], SMMT_Previous.[MODEL RANGE] AS [Previous Model Range], SMMT.[RANGE SERIES], SMMT_Previous.[RANGE SERIES] AS [Previous Range Series], SMMT.VARIANT, SMMT_Previous.VARIANT AS [Previous Variant], SMMT.CC, SMMT_Previous.CC AS [Previous CC], SMMT.[NOM CC], SMMT_Previous.[NOM CC] AS [Previous Nom CC], SMMT.DOORS, SMMT_Previous.DOORS AS [Previous DOORS], SMMT.[BODY TYPE], SMMT_Previous.[BODY TYPE] AS [Previous BODY TYPE], SMMT.TRANSMISSION, SMMT_Previous.TRANSMISSION AS [Previous TRANSMISSION], SMMT.FUEL, SMMT_Previous.FUEL AS [Previous FUEL], SMMT.ASPIRATION, SMMT_Previous.ASPIRATION AS [Previous ASPIRATION], SMMT.[DRIVE TYPE], SMMT_Previous.[DRIVE TYPE] AS [Previous DRIVE TYPE], SMMT.[DRIVING AXLE], SMMT_Previous.[DRIVING AXLE] AS [Previous DRIVING AXLE], SMMT.[FORWARD GEARS] AS [FORWARD GEARS], SMMT_Previous.[FORWARD GEARS] AS [Previous FORWARD GEARS], SMMT.SEATS, SMMT_Previous.SEATS AS [Previous SEATS], SMMT_Previous.[ENGINE MODEL] AS [Previous ENGINE MODEL], SMMT.[NO CYLINDERS], SMMT_Previous.[NO CYLINDERS] AS [Previous NO CYLINDERS], SMMT.[VALVES PER CYLINDER] AS [VALVES PER CYLINDER], SMMT_Previous.[VALVES PER CYLINDER] AS [Previous VALVES PER CYLINDER], SMMT.[VALVE GEAR] AS [VALVE GEAR], SMMT_Previous.[VALVE GEAR] AS [Previous VALVE GEAR], SMMT.[POWER KW], SMMT_Previous.[POWER KW] AS [Previous POWER KW], SMMT.[CALC BHP], SMMT_Previous.[CALC BHP] AS [Previous CALC BHP], SMMT.WHEELBASE, SMMT_Previous.WHEELBASE AS [Previous WHEELBASE], SMMT.GVW, SMMT_Previous.GVW AS [Previous GVW], SMMT.[CAB TYPE], SMMT_Previous.[CAB TYPE] AS [Previous CAB TYPE], SMMT.[VAN ROOF CONFIG], SMMT_Previous.[VAN ROOF CONFIG] AS [Previous VAN ROOF CONFIG], SMMT.[WHEELBASE TYPE], SMMT_Previous.[WHEELBASE TYPE] AS [Previous WHEELBASE TYPE], SMMT.CalcNomCC, SMMT_Previous.CalcNomCC AS [Previous CalcNomCC], IIf([smmt_previous].[variant]=MakeEqualStr([smmt].[variant],[smmt_previous].[variant]),0,1) AS VariantChange, MakeEqualStr([smmt].[variant],[smmt_previous].[variant]) AS MakeEqual, IIf(IsNull([smmt].[variant]) Or IsNull([smmt_previous].[variant]),-1,matchstrings([smmt].[variant],[smmt_previous].[variant])) AS WordsMatched, QrySMMTDifferencesLogicTechnical.Introdiff, QrySMMTDifferencesLogicTechnical.MarqueDiff, QrySMMTDifferencesLogicTechnical.ModelRangeDiff, QrySMMTDifferencesLogicTechnical.RangeSeriesDiff, QrySMMTDifferencesLogicTechnical.CCDiff, QrySMMTDifferencesLogicTechnical.NomCCDiff, QrySMMTDifferencesLogicTechnical.DoorsDiff, QrySMMTDifferencesLogicTechnical.BodyTypeDiff, QrySMMTDifferencesLogicTechnical.TransmissionDiff, QrySMMTDifferencesLogicTechnical.FuelDiff, QrySMMTDifferencesLogicTechnical.AspirationDiff, QrySMMTDifferencesLogicTechnical.DriveTypeDiff, QrySMMTDifferencesLogicTechnical.DrivingAxleDiff, QrySMMTDifferencesLogicTechnical.ForwardGearsDiff, QrySMMTDifferencesLogicTechnical.SeatsDiff, QrySMMTDifferencesLogicTechnical.EngineModelDiff, QrySMMTDifferencesLogicTechnical.NoCylDiff, QrySMMTDifferencesLogicTechnical.ValvespercylDiff, QrySMMTDifferencesLogicTechnical.valvegearDiff, QrySMMTDifferencesLogicTechnical.powerkwDiff, QrySMMTDifferencesLogicTechnical.calcbhpDiff, QrySMMTDifferencesLogicTechnical.gvwDiff, QrySMMTDifferencesLogicTechnical.cabDiff, QrySMMTDifferencesLogicTechnical.vanroofDiff, QrySMMTDifferencesLogicTechnical.wheelbasetypeDiff, QrySMMTDifferencesLogicTechnical.[MVRIS CODE], SMMT.[ENGINE MODEL], 0 AS VariantDiff, QryAbiCWCodeInMatchTableWithNoMatch.[MVRIS CODE], IIf(IsNull([QryAbiCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryAbiCWCodeInMatchTableWithNoMatch]![AbiMatched]=True,"CWCode in match table with no client code","Matched")) AS AbiMatchedStr2, IIf(IsNull([QryAbiCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryAbiCWCodeInMatchTableWithNoMatch]![AbiMatched]=True,False,True)) AS AbiMatchedStr, QryAbiCWCodeInMatchTableWithNoMatch.AbiMatched, IIf(IsNull([QryCapCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"Not Matched",IIf([QryCapCWCodeInMatchTableWithNoMatch]![CapMatched]=True,"CWCode in match table with no client code","Matched")) AS CapMatchedStr2, IIf(IsNull([QryCapCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryCapCWCodeInMatchTableWithNoMatch]![CapMatched]=True,False,True)) AS CapMatchedStr, IIf(IsNull([QryContinentalCWCodeInMatchTableWithNoMatch]![cwCODE]),False,IIf([QryContinentalCWCodeInMatchTableWithNoMatch]![ContMatched]=0,False,True)) AS ContinentalMatchedStr, IIf(IsNull([QryContinentalCWCodeInMatchTableWithNoMatch]![cwCODE]),"Not Matched",IIf([QryContinentalCWCodeInMatchTableWithNoMatch]![ContMatched]=0,"CWCode in match table with no client code","Matched")) AS ContinentalMatchedStr2, IIf(IsNull([QryGlassesCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryGlassesCWCodeInMatchTableWithNoMatch]![GlassMatched]=True,False,True)) AS GlassMatchedStr, IIf(IsNull([QryGlassesCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryGlassesCWCodeInMatchTableWithNoMatch]![GlassMatched]=True,"CWCode in match table with no client code","Matched")) AS GlassMatchedStr2, IIf(IsNull([QryhalfordsCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryHalfordsCWCodeInMatchTableWithNoMatch]![HalfordMatched]=True,False,True)) AS HalfordMatchedStr, IIf(IsNull([QryhalfordsCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"NotMatched",IIf([QryHalfordsCWCodeInMatchTableWithNoMatch]![halfordMatched]=True,"CWCode in match table with no client code","Matched")) AS HalfordMatchedStr2, IIf(IsNull([QryIDSCWCodeInMatchTableWithNoMatch]![MVRISCODE]),False,IIf([QryIDSCWCodeInMatchTableWithNoMatch]![IDSMatched]=True,False,True)) AS IDSMatchedStr, IIf(IsNull([QryIDSCWCodeInMatchTableWithNoMatch]![MVRISCODE]),"Not Matched",IIf([QryIDSCWCodeInMatchTableWithNoMatch]![IDSMatched]=True,"CWCode in match table with no client code","Matched")) AS IDSMatchedStr2, IIf(IsNull([QryMichelinCWCodeInMatchTableWithNoMatch]![CWCODE]),False,IIf([QryMichelinCWCodeInMatchTableWithNoMatch]![MichelinMatched]=0,False,True)) AS MichelinMatchedStr, IIf(IsNull([QryMichelinCWCodeInMatchTableWithNoMatch]![CWCODE]),"Not Matched",IIf([QryMichelinCWCodeInMatchTableWithNoMatch]![MichelinMatched]=0,"CWCode in match table with no client code","Matched")) AS MichelinMatchedStr2, IIf(IsNull([QryTechdocCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),False,IIf([QryTechDocCWCodeInMatchTableWithNoMatch]![TechDocMatched]=True,False,True)) AS TechDocMatchedStr, IIf(IsNull([QryTechDocCWCodeInMatchTableWithNoMatch]![MVRIS CODE]),"Not Matched",IIf([QryTechDocCWCodeInMatchTableWithNoMatch]![TechDocMatched]=True,"CWCode in match table with no client code","Matched")) AS TechDocMatchedStr2, IIf(IsNull([QryvividCWCodeInMatchTableWithNoMatch]![MvrisCODE]),False,IIf([QryvividCWCodeInMatchTableWithNoMatch]![vividMatched]=True,False,True)) AS VividMatchedStr, IIf(IsNull([QryvividCWCodeInMatchTableWithNoMatch]![mvrisCODE]),"Not Matched",IIf([QryvividCWCodeInMatchTableWithNoMatch]![vividMatched]=True,"CWCode in match table with no client code","Matched")) AS VividMatchedStr2, Format(Now(),"dd/mm/yyyy") AS SMMTComparitorDate
FROM ((((((((((SMMT INNER JOIN QrySMMTDifferencesLogicTechnical ON SMMT.[MVRIS CODE] = QrySMMTDifferencesLogicTechnical.[MVRIS CODE]) INNER JOIN SMMT_Previous ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = SMMT_Previous.[MVRIS CODE]) LEFT JOIN QryAbiCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryAbiCWCodeInMatchTableWithNoMatch.[MVRIS CODE]) LEFT JOIN QryCapCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryCapCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryContinentalCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryContinentalCWCodeInMatchTableWithNoMatch.CWCode) LEFT JOIN QryGlassesCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryGlassesCWCodeInMatchTableWithNoMatch.[MVRIS CODE]) LEFT JOIN QryHalfordsCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryHalfordsCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryIDSCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryIDSCWCodeInMatchTableWithNoMatch.MvrisCode) LEFT JOIN QryMichelinCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryMichelinCWCodeInMatchTableWithNoMatch.CWCode) LEFT JOIN QryTechdocCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryTechdocCWCodeInMatchTableWithNoMatch.[Mvris Code]) LEFT JOIN QryVividCWCodeInMatchTableWithNoMatch ON QrySMMTDifferencesLogicTechnical.[MVRIS CODE] = QryVividCWCodeInMatchTableWithNoMatch.MvrisCode;

Open in new window

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Accepted Solution

by:
NurAzije earned 500 total points
ID: 35145755
Try putting the table name in front of every field. The table name in which that field is:
[tablename].[fieldname]

do that everywhere and let me know if it works.


0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question