Rows repeat in a query but the data is not duplicate data (1 Viewer)

[sarahdee]

Hey Everyone! I am new to this site and have a query question that has been driving me nuts! I inherited an Access database (created in Access 2000 probably. We are currently using Access 2010) at work that is used to record machine production data and create reports to show efficiency (among other things). Recently one of the reports (which is based on the offending query) started displaying duplicate rows for two of the machines. The data that is displayed in the report is not duplicate data, it's just displayed twice for some reason. My questions are 1) Why has it started doing this? and 2) How do I make it stop? I'm attaching screen shots of the report, the query, and the query design view. I'm also pasting the SQL for the query into this message. I would send the db but it's too large.

Thanks for any assistance you can provide!

query SQL:

SELECT tblSDS9.Date, tblPeriodDates.Period, tblPeriodDates.Week, tblPeriodDates.Day, DatePart("w",[tblSDS9]![Date]) AS weekday, tblSDS9.Mach, tblSDS9.Spind, tblSDS9.intLot, tblSDS9.[1st shift brks], tblSDS9.[2nd shift brks], tblSDS9.[3rd shift brks], (([tot ends])*216/24/[Spind]) AS [brks/hr2], (IIf([weekday]=4,(IIf(IsNull([1st shift brks]),IIf(IsNull([2nd shift brks]),[3rd shift brks]/12,([2nd shift brks]+[3rd shift brks])/12),IIf(IsNull([2nd shift brks]),IIf(IsNull([3rd shift brks]),[1st shift brks]/12,([1st shift brks]+[3rd shift brks])/24),IIf(IsNull([3rd shift brks]),([1st shift brks]+[2nd shift brks])/12,([1st shift brks]+[2nd shift brks]+[3rd shift brks])/24)))),(IIf(IsNull([1st shift brks]),IIf(IsNull([2nd shift brks]),[3rd shift brks]/12,([2nd shift brks]+[3rd shift brks])/12),IIf(IsNull([2nd shift brks]),IIf(IsNull([3rd shift brks]),[1st shift brks]/10,([1st shift brks]+[3rd shift brks])/24),IIf(IsNull([3rd shift brks]),([1st shift brks]+[2nd shift brks])/12,([1st shift brks]+[2nd shift brks]+[3rd shift brks])/24)))))*(216/[Spind])) AS [brks/hr], Nz([1st shift brks])+Nz([2nd shift brks])+Nz([3rd shift brks]) AS [# brks/day], tblSDS9.[1st shift eff], tblSDS9.[2nd shift eff], tblSDS9.[3rd shift eff], IIf(IsNull([1st shift eff]),IIf(IsNull([2nd shift eff]),[3rd shift eff],([2nd shift eff]+[3rd shift eff])/2),IIf(IsNull([2nd shift eff]),IIf(IsNull([3rd shift eff]),[1st shift eff],([1st shift eff]+[3rd shift eff])/2),IIf(IsNull([3rd shift eff]),([1st shift eff]+[2nd shift eff])/2,([1st shift eff]+[2nd shift eff]+[3rd shift eff])/3))) AS [avg eff], tblSDS9.[white flags], tblSDS9.[blue flags], tblSDS9.[red flags], tblSDS9.[qc flags], tblSDS9.[yellow flags], [white flags]+[blue flags]+[red flags]+[qc flags]+[yellow flags] AS [total flags], tblSDS9.[1st Success], tblSDS9.[1st Fail], tblSDS9.[2nd Success], tblSDS9.[2nd Fail], tblSDS9.[3rd Success], tblSDS9.[3rd Fail], ([1st Success]/([1st Success]+[1st Fail]))*100 AS [1st Doffer Eff], ([2nd Success]/([2nd Success]+[2nd Fail]))*100 AS [2nd Doffer Eff], ([3rd Success]/([3rd Success]+[3rd Fail]))*100 AS [3rd Doffer Eff], IIf(([1st Success]+[1st Fail])=0,0.0000001,(([1st Success]+[3rd Success])/([1st Success]+[1st Fail]+[3rd Success]+[3rd Fail]))*100) AS [Avg Doff Eff], tblSDS9.[1st shift time], tblSDS9.[2nd shift time], tblSDS9.[3rd shift time], IIf(IsNull([2nd shift time]),(Nz([1st shift time])+Nz([2nd shift time])+Nz([3rd shift time]))/2,(Nz([1st shift time])+Nz([2nd shift time])+Nz([3rd shift time]))/3) AS [avg time], Hour([avg time]) AS [avg time hours], Minute([avg time]) AS [avg time minutes], ([avg time hours]*60)+[avg time minutes] AS [tot avg time minutes], tblLotData1.intStdBL, IIf(IsNull([1st shift brks]),IIf(IsNull([3rd shift brks]),0,[3rd shift brks]),IIf(IsNull([3rd shift brks]),[1st shift brks],([1st shift brks]+[3rd shift brks]))) AS [tot ends], Round([brks/hr2]-[intStdBL],2) AS diff
FROM tblPeriodDates RIGHT JOIN (tblLotData1 INNER JOIN tblSDS9 ON tblLotData1.intLot = tblSDS9.intLot) ON tblPeriodDates.date = tblSDS9.Date
WHERE (((tblSDS9.Date)>=Date()) AND ((tblSDS9.Mach)=1 Or (tblSDS9.Mach)=2 Or (tblSDS9.Mach)=3 Or (tblSDS9.Mach)=4 Or (tblSDS9.Mach)=5 Or (tblSDS9.Mach)=6 Or (tblSDS9.Mach)=7 Or (tblSDS9.Mach)=8 Or (tblSDS9.Mach)=9 Or (tblSDS9.Mach)=10 Or (tblSDS9.Mach)=11 Or (tblSDS9.Mach)=12));

 

[MSAccessRookie]

You might want to examine the data in the Tables tblSDS9 and tblLotData1. Your design exhibit indicates that they are supposed to have a 1:1 relationship. The other exhibits would indicate that perhaps they do not. If any row is repeated in either table, then all rows associated with it in the JOIN could be repeated in any output.

 

[sarahdee]

Thanks for the fast response! Would the fact that the IntLotData1 table is a linked table have any bearing on things? I looked at the data in both tables that you mentioned in your reply and found that the IntLotData1 table is actually linked to another db in different folder. I found the folder and checked the table data in its native location and it is indexed with no duplicates allowed on the primary key field but not on the IntLot field. In fact, the field that is joined is not even the one that is used in the query. The IntLot field that is used in the query is from tblSDS9. The field from the IntLotData1 table that's used in the query is IntStdBL. That makes me wonder if the join is on the wrong field altogether, but if so why has this just cropped up. This db has been in use since 2001, but this issue is recent (within the last month). Gotta love it!!

 

[MSAccessRookie]

Thanks for the fast response! Would the fact that the IntLotData1 table is a linked table have any bearing on things? I looked at the data in both tables that you mentioned in your reply and found that the IntLotData1 table is actually linked to another db in different folder. I found the folder and checked the table data in its native location and it is indexed with no duplicates allowed on the primary key field but not on the IntLot field. In fact, the field that is joined is not even the one that is used in the query. The IntLot field that is used in the query is from tblSDS9. The field from the IntLotData1 table that's used in the query is IntStdBL. That makes me wonder if the join is on the wrong field altogether, but if so why has this just cropped up. This db has been in use since 2001, but this issue is recent (within the last month). Gotta love it!!

I do not believe that the fact that the Table is linked is affecting the situation in any way. In addition, from what you are saying, the Field IntLot probably does not matter either. The only thing that does matter is the Field (or Fields) that the Tables link on. If they have duplicate values, then your condition could be the result. Look into that possibility.

 

[sarahdee]

Hey! I wanted to say thank you for your help. I took a closer look at the lot numbers in teh IntLotData1 table and found six records that had duplicate lot numbers. I am in the process of deleting the lot numbers that are no longer in use and them I am going to index the IntLot field so that this doesn't happen again. Why it wasn't done to begin with is beyond me, but at least we won't have to worry about it any more.

Again, thanks so much for you help!

 

[MSAccessRookie]

Always a pleasure to know that there is a Happy Ending.