By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,264 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,264 IT Pros & Developers. It's quick & easy.

Help with mixed up data in query

P: 29
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:

Expand|Select|Wrap|Line Numbers
  1. tblChangeOut.equipmentID    compartmentName    compartmentID    partID
  2. 31780    Water Pump    13    7
  3. 31780    Water Pump    13    11
  4. 31780    TurboCharger    11    7
  5. 31780    TurboCharger    11    11
  6. 31780    Travel Motors    17    7
  7. 31780    Travel Motors    17    11
  8. 31780    Swing Pump    23    7
  9. 31780    Swing Pump    23    11
  10. 31780    Swing Motor    18    7
  11. 31780    Swing Motor    18    11
  12. 31780    Swing Drive    20    7
  13. 31780    Swing Drive    20    11
  14. 31780    Starter    15    7
  15. 31780    Starter    15    11
  16. 31780    Pump Drive    22    7
  17. 31780    Pump Drive    22    11
  18. 31780    Piston Pump    39    7
  19. 31780    Piston Pump    39    11
  20. 31780    Pilot Pump    24    7
  21. 31780    Pilot Pump    24    11
  22. 31780    Fuel Tr. Pump    12    7
  23. 31780    Fuel Tr. Pump    12    11
  24. 31780    Final Drive    19    7
  25. 31780    Final Drive    19    11
  26. 31780    Engine    7    7
  27. 31780    Engine    7    11
  28. 31780    Brakes    40    7
  29. 31780    Brakes    40    11
  30. 31780    Alternator    14    7
  31. 31780    Alternator    14    11
Actual Data dump I'm getting:
Expand|Select|Wrap|Line Numbers
  1. tblChangeOut.equipmentID    confirmed    Expr1002    readingDate    Hour    warDateChange    unitName    companyName    warYearsAfter    warYears    warDate    hours    compartmentName    Perc    compartmentID    partID    hoursChangedAt    tblCompChange.equipmentID    itemNumbers
  2. 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    
  3. 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    
  4. 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    
  5. 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    
  6. 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    
  7. 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    
  8. 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    
  9. 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    
  10. 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    
  11. 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    
  12. 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    
  13. 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    
  14. 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    
  15. 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    
  16. 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    
  17. 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    
  18. 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    
  19. 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    
  20. 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    
  21. 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    
  22. 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    
  23. 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    
  24. 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    
  25. 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    
  26. 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    
  27. 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    
  28. 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    
  29. 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    
  30. 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    
  31. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. ORDER BY tblChangeOut.equipmentID, tblCompartment.compartmentName DESC;
May 31 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.