proper/multiplying values in query

[ttuser4]

i have a query:

SELECT DISTINCTROW OUTTURN.LOT, Sum(OUTTURN.VOLUME) AS PRODUCED, Sum(LUMLOT.VOLUME) AS SOLD, PRODUCED-SOLD AS UNSOLD, Sum(LUMIN.VOLUME) AS INV, UNSOLD-INV AS DIF, Sum(LUMIN.VALUE) AS INV_VALUE, INV_VALUE/INV*DIF AS VAL_DIF
FROM (OUTTURN INNER JOIN LUMLOT ON OUTTURN.LOT = LUMLOT.LOT) INNER JOIN LUMIN ON LUMLOT.LOT = LUMIN.LOT
GROUP BY OUTTURN.LOT, LUMLOT.LOT, LUMIN.LOT;

the problem is that values PRODUCED, SOLD and INV are not summarized correctly but they are multiply by number of instances of LOT value (for example if LOT is twice in OUTTURN then PRODUCED value is doubled, etc.)

i just want to summarize products produced, invoiced and compare it with inventory.

any idea how to fix this, please?

 

[alvechurchdata]

Might it be because you're grouping on three different LOT fields? Try changing it to just [TT]... GROUP BY LOT[/TT]

If that doesn't help, split it into two queries. Start by joining the three input tables in the first query and then write a second query to do the grouping. Seeing the intermediate results may help you solve the problem.

Geoff Franklin

http://www.alvechurchdata.co.uk