Hi everyone,

I am pulling my hair out trying to figure this out. I am hoping someone can shed some light on this for me.

I have three tables from which I am trying to pull numbers using SUM.
I am trying to retrieve total Cost, Sell and Rebate numbers for each account manager in a given date range. So here are my tables and they link up.

Table 1 : ORDERS - has the order info and contains the AcctMgr field.

Table 2 : ITEMS - has the Units, Cost and Sell fields. Links to ORDERS with an OrderNo field.

Table 3 : REBATES - has the RebateAmt field. Links to ITEMS with a SKey field.

ORDERS has one record for every order which links to ITEMS that can have many records for one Order which links to REBATES which can have many records to link to one Item.

The following query will yeild the correct results provided that each Item has only record(or none) in REBATES. However if there is more than one record in REBATES the results are doubled, tripled and so on.

SELECT AcctMgr, SUM(Units * Cost) AS TotCost, SUM(Units * Sell) AS TotSell, SUM(Units * Rebates)
FROM ORDERS, ITEMS, REBATES
WHERE ITEMS.InvDate>='Aug 1, 2003' AND ITEMS.InvDate < 'Sep 1, 2003' AND ORDERS.OrderNo=ITEMS.OrderNo AND Items.SKey*=REBATES.SKey
GROUP BY AcctMgr
ORDER BY AcctMgr

Does anyone have an idea how I can get a one line per AcctMgr result set with the correct summations?

Thanks so much for any input!

If the duplicated rows are identical I sudgest you put a DISTINCT keyword after the SELECT.

SELECT DISTINCT AcctMng .....


Cheers,
Suren.