Duplicate records

[Gismo]

Hi all,

I have the strangest result from a query, I must be missing something here but I have done the a thousand time with the correct output

I have a query based on a combobox
This query could have thousands of records

I have a second query from a second combobox
This will only contain a few records but all these records will be present in the first query

Basically the second query is a filter for the first query

When I use the blow 3 rd query, I get doubles on doubles

The one specific record I am looking at was entered by the engineer, same part number with same quantity (1). He does indeed require 2

The i run the 1st and 2nd query, I get the result of the 2 same part numbers loaded with qty 1 each

When I join the 2 queries, my result is 4 of the same records

I do not have the 3rd query as sum, and refer not too

If any of the records were entered in 1 line, I do net get the duplication

SELECT DISTINCTOW [Stock QRY].[Aircraft Registration], [Stock QRY].SN, [Stock QRY].[System status], [Stock QRY].[PO No], [Order Status].[Order Status], [Stock QRY].Revision, [Stock QRY].[IW73 TBL].Order, [Stock QRY].OpAc, [Stock QRY].Description, [Stock QRY].Description1, [Stock QRY].[Operation short text], [Stock QRY].StartDate, [Stock QRY].[Reqmt Date], [Stock QRY].Material, [Stock QRY].[Material Number], [Stock QRY].BUM, [Stock QRY].[Qty Withdrawn], [Stock QRY].[Stock LA01], [Stock QRY].[Unrestricted LA02], [Stock QRY].[Stock LA04], [Stock QRY].[Unrestricted LA06], [Stock QRY].[Unrestricted LA10], [Stock QRY].[Unrestricted LA12], [Stock QRY].[Unrestricted LA14], [Stock QRY].[Stock LA15], [Stock QRY].[Reqmt Qty1], [Stock QRY].[Qty Withdrawn1], [Stock QRY].Missed, [Stock QRY].[Stock Avilable], [Stock QRY].[Bal Req/Drawn], [Stock QRY].[Status - Stock], [Stock QRY].[Status - LA01], [Stock QRY].[Status - LA04], [Stock QRY].[Status - LA15], [Stock QRY].[Reqmt Qty], [Stock QRY].EDD, [Stock QRY].[Eff#MatPlg]
FROM ([Stock QRY] INNER JOIN [Registration - CS - Stock] ON ([Stock QRY].[Aircraft Registration] = [Registration - CS - Stock].Registration) AND ([Stock QRY].[IW73 TBL].Order = [Registration - CS - Stock].Order) AND ([Stock QRY].Material = [Registration - CS - Stock].Material)) LEFT JOIN [Order Status] ON [Stock QRY].Type = [Order Status].[Order Type Sap]
WHERE ((([Stock QRY].[Aircraft Registration])="bps-03") AND (([Stock QRY].[System status]) Like "*REL*") AND (([Stock QRY].[IW73 TBL].Order)="85861") AND (([Stock QRY].[Operation short text])="(00123) Hose Replacement"))
ORDER BY [Stock QRY].[Aircraft Registration], [Stock QRY].Revision, [Stock QRY].[IW73 TBL].Order, [Stock QRY].OpAc, [Stock QRY].Material;

 

[Pat Hartman]

Your joins are not PK to FK. You are joining data field to data field. Therefore, when the join fields in tblA are not unique and the join fields in tblB are not unique, you will multiply your results. Ie tblA * tblB. So if tblA has 3 rows with identical values and tblB has 4, then the result is 3 * 4 or 12 duplicate rows.

It is really poor practice to design tables without primary keys and to not create foreign keys in the child tables on which they can be joined.

When you are working with downloaded data, you get what you get and so you have to decide how best to resolve the problem. What data other then the join fields do you need (I'm not going to try to work this out myself). You might get a simple resolution by changing to "Select Distinct". You might need to use a totals query to group one of the tables to reduce it to a single row per set of join fields so that instead of multiple rows you have one, perhaps with a count.

 

[Gismo]

Thank you
This solved the problem
I import from SAP
So I created my own unique number and then linked between the 2 queries
Works perfect

 

[Pat Hartman]

It always annoys me when this type of download doesn't include the proper join fields. Not sure how you assigned the unique numbers but OK. I'm happy if you are