Sum problem.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Below query suppose to show result sum of Gross, Discount & Net group by date and sname but it does not. The should be only only one line only for a date for a sname. Where is my mistake. Pls advise

SELECT staff.sname, TNLINES.date, TNLINES.docket, Sum([Qty]*[fileprice]) AS Gross, Sum([Qty]*[disc_valpc]) AS Discount, Sum(([Qty]*[fileprice])-([Qty]*[disc_valpc])) AS Ne
FROM (staff INNER JOIN tnheader ON staff.staff = tnheader.staff) INNER JOIN TNLINES ON tnheader.docket = TNLINES.docke
GROUP BY staff.sname, TNLINES.date, TNLINES.docket, TNLINES.sk
HAVING (((TNLINES.sku)<>"100000000000"))

Mark
 
What in the data is being repeated??





Mark said:
Hi,
Below query suppose to show result sum of Gross, Discount & Net group by
Click to expand...
date and sname but it does not. The should be only only one line only for a
date for a sname. Where is my mistake. Pls advise.
SELECT staff.sname, TNLINES.date, TNLINES.docket, Sum([Qty]*[fileprice])
Click to expand...
AS Gross, Sum([Qty]*[disc_valpc]) AS Discount,
Sum(([Qty]*[fileprice])-([Qty]*[disc_valpc])) AS Net
FROM (staff INNER JOIN tnheader ON staff.staff = tnheader.staff) INNER
Click to expand...
JOIN TNLINES ON tnheader.docket = TNLINES.docket
 
Hi,


Inner join may amplify the number of records, in fact, the record
would be repeated for EACH match. There is probably more than ONE match in
each table for staff.staff = tnheader.staff, for a given staff, or more than
one match, in each table, tnheader.docket = TNLINES.docket, for a given
docket. When that occur, you have a many-to-many join, and the records get
duplicated.



Table1
f1, f2
a 1
a 2



Table2
g1, g2
a 10
a 20
b 30



SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON table1.f1=table2.g1


returns


f1 f2 g1 g2
a 1 a 10
a 1 a 20
a 2 a 10
a 2 a 20



where you can see that both tables see some of their records "duplicated":
( a, 1) from table1 appear twice, and also ( a, 20) from Table2. The sum
over the duplicated values won't be the same as if there was no duplicated
values, evidently.


Only if you have a one-to-one match would you have absolutely no
duplication, in any of the involved tables.


Hoping it may help,
Vanderghast, Access MVP





Mark said:
Hi,
Below query suppose to show result sum of Gross, Discount & Net group by
Click to expand...
date and sname but it does not. The should be only only one line only for a
date for a sname. Where is my mistake. Pls advise.
SELECT staff.sname, TNLINES.date, TNLINES.docket, Sum([Qty]*[fileprice])
Click to expand...
AS Gross, Sum([Qty]*[disc_valpc]) AS Discount,
Sum(([Qty]*[fileprice])-([Qty]*[disc_valpc])) AS Net
FROM (staff INNER JOIN tnheader ON staff.staff = tnheader.staff) INNER
Click to expand...
JOIN TNLINES ON tnheader.docket = TNLINES.docket
 
Back
Top