Hello,
I've been looking at this for probably five hours now and I'm loosing my mind.
I'll start by just showing my end result of this query of mine, since it illustrates the problem.
Here is my Database structure:
I'm trying to get a huge datadump out of it. It's confusing but it's posted below. It's confusing enough that let me just quickly mention the problem. the tblCompChange table keeps repeating. 7 and 11 represent the "compartmentID" 's for and Engine and transmission and it's indicating in tblCompChange that they were changed out of a specific unit. This data dump is supposed to print out all of the components in this particular piece of equipment and any information if they were changed out at any point, and it normally does that just fine (minus the change out data) - but when I add that tblCompChange into the picture every single component in that peice of equipment gets listed for as many times as there are entries for that piece of equipment in the tblCompChange table. Removing that table fixes the issue - but I need to know if components in our equipment was changed out and when... I'm not sure what to do.
God I hope that's not too confusing, let me know if theres anything I can do to clear that up.
End result with useless data cut out that might be less confusing to look at:
Actual Data dump I'm getting:
Query:
I've been looking at this for probably five hours now and I'm loosing my mind.
I'll start by just showing my end result of this query of mine, since it illustrates the problem.
Here is my Database structure:
I'm trying to get a huge datadump out of it. It's confusing but it's posted below. It's confusing enough that let me just quickly mention the problem. the tblCompChange table keeps repeating. 7 and 11 represent the "compartmentID" 's for and Engine and transmission and it's indicating in tblCompChange that they were changed out of a specific unit. This data dump is supposed to print out all of the components in this particular piece of equipment and any information if they were changed out at any point, and it normally does that just fine (minus the change out data) - but when I add that tblCompChange into the picture every single component in that peice of equipment gets listed for as many times as there are entries for that piece of equipment in the tblCompChange table. Removing that table fixes the issue - but I need to know if components in our equipment was changed out and when... I'm not sure what to do.
God I hope that's not too confusing, let me know if theres anything I can do to clear that up.
End result with useless data cut out that might be less confusing to look at:
Code:
tblChangeOut.equipmentID compartmentName compartmentID partID 31780 Water Pump 13 7 31780 Water Pump 13 11 31780 TurboCharger 11 7 31780 TurboCharger 11 11 31780 Travel Motors 17 7 31780 Travel Motors 17 11 31780 Swing Pump 23 7 31780 Swing Pump 23 11 31780 Swing Motor 18 7 31780 Swing Motor 18 11 31780 Swing Drive 20 7 31780 Swing Drive 20 11 31780 Starter 15 7 31780 Starter 15 11 31780 Pump Drive 22 7 31780 Pump Drive 22 11 31780 Piston Pump 39 7 31780 Piston Pump 39 11 31780 Pilot Pump 24 7 31780 Pilot Pump 24 11 31780 Fuel Tr. Pump 12 7 31780 Fuel Tr. Pump 12 11 31780 Final Drive 19 7 31780 Final Drive 19 11 31780 Engine 7 7 31780 Engine 7 11 31780 Brakes 40 7 31780 Brakes 40 11 31780 Alternator 14 7 31780 Alternator 14 11
Code:
tblChangeOut.equipmentID confirmed Expr1002 readingDate Hour warDateChange unitName companyName warYearsAfter warYears warDate hours compartmentName Perc compartmentID partID hoursChangedAt tblCompChange.equipmentID itemNumbers 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Water Pump 1.8184 13 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Water Pump 1.8184 13 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 TurboCharger 1.8184 11 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 TurboCharger 1.8184 11 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Travel Motors 1.51533333333333 17 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Travel Motors 1.51533333333333 17 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 8000 Swing Pump 1.1365 23 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 8000 Swing Pump 1.1365 23 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Swing Motor 1.51533333333333 18 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Swing Motor 1.51533333333333 18 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 12000 Swing Drive 0.757666666666667 20 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 12000 Swing Drive 0.757666666666667 20 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Starter 1.8184 15 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Starter 1.8184 15 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Pump Drive 0.9092 22 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Pump Drive 0.9092 22 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Piston Pump 1.51533333333333 39 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 6000 Piston Pump 1.51533333333333 39 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 8000 Pilot Pump 1.1365 24 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 8000 Pilot Pump 1.1365 24 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Fuel Tr. Pump 1.8184 12 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Fuel Tr. Pump 1.8184 12 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Final Drive 0.9092 19 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Final Drive 0.9092 19 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 1 2 12:00:00 AM 10000 Engine 0.9092 7 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 1 2 12:00:00 AM 10000 Engine 0.9092 7 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Brakes 0.9092 40 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 10000 Brakes 0.9092 40 11 700 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Alternator 1.8184 14 7 1000 31780 31780 No 12:00:00 AM 5/1/2010 9092 5/5/2009 235B Caterpillar 12:00:00 AM 5000 Alternator 1.8184 14 11 700 31780
Query:
Code:
SELECT tblChangeOut.equipmentID, tblChangeOut.confirmed, tblChangeOut.warDate, tblChangeOut.readingDate, tblChangeOut.Hour, tblCompChange.warDateChange, tblCompartment.compartmentID, tblVendorEquip.unitName, tblSource.companyName, tblBench.warYearsAfter, tblBench.warYears, tblChangeOut.warDate, tblBench.hours+tblBench.siteAdjust AS hours, tblCompartment.compartmentName, [hour]/[hours] AS Perc, tblCompChange.partID, tblCompChange.hoursChangedAt, tblCompChange.equipmentID, tblBench.itemNumbers FROM ((tblSource INNER JOIN tblVendorEquip ON tblSource.companyID = tblVendorEquip.companyID) INNER JOIN (tblCompartment INNER JOIN tblBench ON tblCompartment.compartmentID = tblBench.compartmentID) ON tblVendorEquip.unitID = tblBench.unitID) INNER JOIN (tblChangeOut LEFT JOIN tblCompChange ON tblChangeOut.equipmentID = tblCompChange.equipmentID) ON tblVendorEquip.unitID = tblChangeOut.unitID ORDER BY tblChangeOut.equipmentID, tblCompartment.compartmentName DESC;