Query problem - Date issues - too many records

[stevebanks]

Hey people,

Having a little problem. I have two tables, stock and reorder. The stock table has just one entry for each item of stock with an amount, amountbackordered, and amountcommitted. The reorder table has multiple entries for the same items - different (past and present) order dates, and in some cases 3 ordered quantities for delivery on the same date i.e 4 + 6 + 8 but 3 entries for it... I need to be able to get the delivery number on order and its expected delivery date, per item of stock.

I am struggling. I did have

SELECT stock.number, stock.units, stock.bounits, stock.commited, reorder.number, reorder.quantity, reorder.expect_on
FROM puritem RIGHT JOIN stock ON reorder.number = stock.number;

but that's giving me multiple records per stock item where i only want the one (and if requiring a delivery showing how many are being delivered and when)

this is our company system that I am logging into via ODBC so I cannot alter anything from the tables unfortunately...

Please help!!! thank you

 

[Golom]

Try

SELECT S.number, S.units, S.bounits, S.commited, 
       SUM(R.quantity) As [Quantity Ordered],
       R.expect_on

FROM reorder R RIGHT JOIN stock S ON R.number = S.number

GROUP BY S.number, S.units, S.bounits, S.commited, R.expect_on

I have eliminated reorder.number because it is probably different for each reorder and would result in the same duplication of records that you are now getting.

You can extend this by including fields of the form

SUM(R.Amount) As [TotalAmount]

if there are other fields in the reorder table that you want summed.

I assumed that FROM puritem was really supposed to be FROM reorder

 

[stevebanks]

Thanks, I'll give it a go