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));
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));