Query Confusion

[sue0912]

Hi everyone,

I am trying to create a report that takes a table of our customers that own equipment, then match it up to a Parts table that shows what type of parts they have purchased (Product Code). So the output displays the customer, all the machines they own and then in a grouped format the product code with total price

Example Product1 Product2 Product3
Customer A 0.00 50.00 75.00
Machine 1
Machine 2
Machine 3

So My problem is in my query (below) I keep getting dups of the machines and the total price is only giving me the last entry.

Query (I left out a group by as I want to understand where my mistakes are)

SELECT Parts_GL_TableF.Customer, Equip_List_F.ItemDescription, 
Parts_GL_TableF.Prod_Line, Parts_GL_TableF.Qty, Parts_GL_TableF.Ext_Amnt  

FROM Parts_GL_TableF LEFT OUTER JOIN ED.dbo.Equip_List_F Equip_List_F  ON 
Parts_GL_TableF.Customer = Equip_List_F.Customer

WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And 
Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN 
('ADPT',  'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 
'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE',  'FABR', 'FILT', 'FINL', 
'GRID', 'HAZE', 'HOSE', 'HW',   'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 
'NSHW',   'NSSC',  'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL',  
'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 
'TESB', 'TRAC', 'TROM', 'TS',  'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 
'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S') 

ORDER BY Parts_GL_TableF.Customer,  Equip_List_F.ItemDescription,  
Parts_GL_TableF.Prod_Line

Example Data of parts table
Customer Prod_Line Qty Ext_Amnt
A B C CRUSHED BEAR 1 465
A B C CRUSHED BEAR 1 255.61
A B C CRUSHED BEAR 1 465
A B C CRUSHED BEAR 2 156
A B C CRUSHED BEAR 1 337.21

Example Data of Equip Table
ItemNumber Customer
POWERGRID 800 SN:7219836 A B C CRUSHED MATERIALS

Example of Data my query outputs
A B C CRUSHED POWERGRID 800 SN:7219836 BEAR 1.0
337.20999999999998
A B C CRUSHED POWERGRID 800 SN:7219836 BELT 1.0 1421.0
A B C CRUSHED POWERGRID 800 SN:7219836 CRBL 1.0
3.7994.530000000000001
A B C CRUSHED POWERGRID 800 SN:7219836 NUTS 1.0
46.399999999999999
A B C CRUSHED POWERGRID 800 SN:7219836 NUTS 1.0
39.880000000000003
A B C CRUSHED POWERGRID 800 SN:7219836 ROLL 2.0
559.25999999999999
A B C CRUSHED POWERGRID 800 SN:7219836 WPPG 1.0
989.76999999999998
A B C CRUSHED POWERGRID 800 SN:7219836 WPPG 1.0
2066.6700000000001

What am I not joining correctly?

 

[sue0912]

Was it to much info? Should I rewrite it? Sorry.

Sue

 

[sue0912]

Ok...I did some tweeking and am almost there but need some help.

Query is
SELECT Parts_GL_TableF.Customer,
Equip_List_F.ItemDescription , Parts_GL_TableF.GL_Accnt,
Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty) ,
SUM(Parts_GL_TableF.Ext_Amnt) FROM Parts_GL_TableF
LEFT OUTER JOIN Equip_List_F ON Parts_GL_TableF.Customer =
Equip_List_F.Customer WHERE
((Parts_GL_TableF.Inv_Date>='01/01/2009' And
Parts_GL_TableF.Inv_Date<=GetDate())) AND
Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT',
'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL',
'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT',
'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS',
'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO',
'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL',
'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE',
'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL',
'WASH', 'WHEL', 'WPPG', 'WPPR') AND
Parts_GL_TableF.Customer NOT IN ('A C S') GROUP BY
Parts_GL_TableF.Customer, Equip_List_F.ItemDescription,
Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt
ORDER BY Parts_GL_TableF.Customer,
Equip_List_F.ItemDescription, Parts_GL_TableF.Prod_Line,
Parts_GL_TableF.GL_Accnt

Output is
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 BEAR 1.0 337.20999999999998
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 BELT 1.0 1421.0
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 CRBL 1.0 94.530000000000001
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 NUTS 2.0 86.280000000000001
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 ROLL 2.0 559.25999999999999
A B C CRUSHED MATERIALS POWERGRID 800 SN:7219836
30300-30-100-000 WPPG 2.0 3056.4400000000001

All is good but how do I tweek the query so that ItemDescription field on displays 1 (Powergrid 800 SN: 7219836)

Thanks
Sue

 

[sue0912]

ok...i am stuck. Am I approaching this all incorrectly?

When I look at my output, I can see where I can select just the itemdescription once...BUT what do I do when the Customer may have 2 difference item descriptions...

any help would be appreciated...please

Sue