PeterBaileyUk
asked on
union query says field could refer to more than one table
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.