incorrect sum from query

[njack217]

Heres an interesting bug I found while running a query access seems to
be adding an additional instance of the first (and/or possibly a
subsequent) value into my sums. the query is like this.

SELECT pum5063.[vendor code], Sum(pum5063.[qty ordered]) AS [qty
ordered], Sum(pum5063.[qty rcvd]) AS [SumOfqty rcvd], Count(pum5063.
[rcpt nmbr]) AS [CountOfrcpt nmbr]
FROM pum5063 RIGHT JOIN productaplist ON pum5063.[item number] =
productaplist.[item code]
GROUP BY pum5063.[vendor code]
ORDER BY pum5063.[vendor code];

data looks like this (it's tab delimited copy and paste it to see it
clearly)

prod code vendor code name PO rcpt nmbr item number promise date qty
ordered receipt date qty rcvd frz cost unit price ext price
03481C ABSCO LTD 205252 345835 68-HF219 4/26/2007 500 3/12/2007 500
5.27 5.62 2634.3
I should point out that all of the fields except the vendor code will
change record by record. This particular vendor number sums to a total
of 3,920on SumOfqty rcvd (500 higher than actual total of values: 500,
500, 30, 40, 20, 100, 100, 30, 50, 500, 90, 100, 60, 120, 30, 30, 60,
60, 60, 60, 60, 540, 90, 90, 90, 10) anyone have any ideas on what may
be going on here?

Thanks,
Nate

 

[Guest]

The assumption would be that the outer join is causing the values from one or
more rows from pum5063 to be double counted, which suggests that there night
be duplicate item code values in productaplist. I don't see why you have the
join anyway, as no columns from productaplist are being made use of. I'd
have thought the following should do it:

SELECT
[vendor code],
SUM([qty ordered]) AS [SumOfqty ordered],
SUM([qty rcvd]) AS [SumOfqty rcvd],
COUNT(*) AS [CountOfrcpt nmbr]
FROM pum5063
GROUP BY [vendor code];

Ken Sheridan
Stafford, England

 

[njack217]

The join is there to filter out data that is included in the table
pum5063 that is not desired to be in the data which will be used in
reports and totaled down the line. However you are spot on with your
conclusion! I will filter these out in a seperate query.
Thanks alot,
Nate