Query started showing duplicates. Didn't before.

[Poco_90]

Hi,
I inherited a database recently and all was fine until about a week ago. The database that is giving trouble generates a report using a query. The query looks up a table and generates the data so it can be shown in a table and chart on a report.

The query lists all the different areas on the factory floor and the machines within each area as well as other information. My problem is one area (there are 16) is showing duplicates when the query is run. These duplicates are not present in the table.

I haven't changed the query or even touched it for it to "break" like this. I am just wondering if any of you could see anything in the SQL that might be causing this. Thanks in advance,
Poco



SELECT Details.Date, Details.TopLevel, Details.[Part Number], Details.Shift, Details.Machine, Details.Quantity, Left([Details.Operator],1) & IIf(InStr(1,[Details.Operator],"")<>0,Mid([Details.Operator],InStr(1,[Details.Operator]," "),2),"") AS Op, Details.[Total Scrap], Details.[Working Time], Details.Area, PartNumbers.Description, AreaFaults.Group1, AreaFaults.Group2, AreaFaults.Group3, AreaFaults.Group4, AreaFaults.Group5, Details.Grp1, Details.Grp2, Details.Grp3, Details.Grp4, Details.Grp5, Sum(Details.[Total Scrap]/Details.Quantity)*100 AS PScrap, Sum(Details.Grp1/Details.Quantity)*100 AS G1PScrap, Sum(Details.Grp2/Details.Quantity)*100 AS G2PScrap, Sum(Details.Grp3/Details.Quantity)*100 AS G3PScrap, Sum(Details.Grp4/Details.Quantity)*100 AS G4PScrap, Sum(Details.Grp5/Details.Quantity)*100 AS G5PScrap, Details.ReworkQty, Details.DateProduced, Left([Details.ProducedBy1],1) & IIf(InStr(1,[Details.ProducedBy1],"")<>0,Mid([Details.ProducedBy1],InStr(1,[Details.ProducedBy1]," "),2),"") AS Op1, Left([Details.ProducedBy2],1) & IIf(InStr(1,[Details.ProducedBy2],"")<>0,Mid([Details.ProducedBy2],InStr(1,[Details.ProducedBy2]," "),2),"") AS Op2, Details.Comment, Details.PK_ID, Details.[Total Time], Details.ENTEREDTIME, Details.Toplevel & " " & Details.ProductSeries & PartNumbers.Description AS TL
FROM MaxDate, Details, AreaFaults INNER JOIN PartNumbers ON AreaFaults.Area = PartNumbers.Area
WHERE (((Details.Date)=[MaxDate].[MaxOfDate]) AND ((PartNumbers.Part)=[Details].[Part Number]) AND ((PartNumbers.TopLevel)=[Details].[Toplevel]) AND ((PartNumbers.Area)=[Details].[Area]))
GROUP BY Details.Date, Details.TopLevel, Details.[Part Number], Details.Shift, Details.Machine, Details.Quantity, Details.[Total Scrap], Details.[Working Time], Details.Area, PartNumbers.Description, AreaFaults.Group1, AreaFaults.Group2, AreaFaults.Group3, AreaFaults.Group4, AreaFaults.Group5, Details.Grp1, Details.Grp2, Details.Grp3, Details.Grp4, Details.Grp5, Details.ReworkQty, Details.DateProduced, Details.Comment, Details.PK_ID, Details.[Total Time], Details.ENTEREDTIME, Details.Toplevel & " " & Details.ProductSeries & PartNumbers.Description, Details.Operator, Details.ProducedBy1, Details.ProducedBy2
ORDER BY Details.Area, Details.Machine, Details.TopLevel;

 

[Guus2005]

Check if either MaxDate or Details have more than one record in 'm.

Because the way you *don't* join these two tables you create a cartesian product resulting all records to multiply by the number of records in these two tables.

HTH

 

[Poco_90]

Hi Guus2005,
Apologies in the delay getting back to you. Thanks for your suggestion.

One of the tables the query was running on had a couple of duplicates in it causing the problems for me. It is all sorted again.
Thanks again for your help.