query doubles some records but not all

[illusionek]

Hello!

I have a problem with below query. It doubles some records. I kind of found the issue but I do not know how to fix it.

Basicially I have two tables, which are Linked tables and they are linked to two Excel files. Each of the tables has almost the same information: Stock Number, Location and Lot.

The purpose of this query is to check Quantities on both spreadsheets to see if there are any discrepancies. Query works fine if Stock Number, Location and Lot are the same on both spreadsheets but if they differ somehow i.e. Stock Number and Location are the same but Lot is different then Access doubles/triples records.

I have a feeling there is an error in Relationships. I googled it and I may need to set-up many-to-many relationship but the problem is that Access does not alow me to do it. I created a junction table but then I cannot set-up one-to-many relationship between tables, which I think is because tables are Linked. :banghead:


Please help

SELECT DISTINCT [Query Union].[Stock Code] AS SKU, [Query Union].[Location Id] AS [Location No], [Query Union].[Lot No#] AS Lot, IIf(IsNull([TMP2 SOH].[Physical stock]),0,CDbl([TMP2 SOH].[Physical stock])) AS [TMP2 Qty], IIf(IsNull([TMP1 SOH].[Good Stock]),0,CDbl([TMP1 SOH].[Good Stock])) AS [TMP1 Qty], [TMP1 Qty]-[TMP2 Qty] AS [Variance (TMP1 - TMP2)]
FROM ([Query Union] LEFT JOIN [TMP2 SOH] ON ([Query Union].[Stock Code] = [TMP2 SOH].[Item number]) AND ([Query Union].[Location Id] = [TMP2 SOH].[License Plate Id]) AND ([Query Union].[Lot No#] = [TMP2 SOH].[Lot number])) LEFT JOIN [TMP1 SOH] ON ([Query Union].[Stock Code] = [TMP1 SOH].[Stock Code]) AND ([Query Union].[Location Id] = [TMP1 SOH].[Location Id]) AND ([Query Union].[Lot No#] = [TMP1 SOH].[Lot No#]);

 

[JHB]

..
Query works fine if Stock Number, Location and Lot are the same on both spreadsheets but if they differ somehow i.e. Stock Number and Location are the same but Lot is different then Access doubles/triples records.

The query does exactly what you tell it to do.
If all values are the same then show one record, if one or more values differ, then show all combination!
So what exactly do you want and when, (criteria)?

 

[illusionek]

At the moment it shows the same record two or three times. So all values are the same but I get two rows with exactly the same values.

 

[spikepl]

You probably have ALL in your UNION - remove it.

 

[illusionek]

I dont. I checked Union and both tables and there are no duplications. The problem only starts when I join them together.

 

[illusionek]

Problem solved I missed a statment in Where to exclude blank rows :banghead:

Thanks a lot for all help.