One to Many Report Improperly Summing

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

Guest

I have a Left Join Query between 2 tables. Table one has an id number and a total amount sold - both unique items. Table two has id number and expenses related to the sale - multiple items.
For example:
Table 1 : Item1 Amount $ 100
Table 2 : Item1 exp1 $ 5
Table 2 : Item1 exp2 $ 3
Table 2 : Item1 exp3 $ 9

When I try todo summation in my report, I get the correct sum of $17 for expenses, but $300 for my Amount Sold.
Any suggestions? Thanks!
 
-----Original Message-----
I have a Left Join Query between 2 tables. Table one has
Click to expand...
an id number and a total amount sold - both unique
items. Table two has id number and expenses related to
the sale - multiple items.
For example:
Table 1 : Item1 Amount $ 100
Table 2 : Item1 exp1 $ 5
Table 2 : Item1 exp2 $ 3
Table 2 : Item1 exp3 $ 9

When I try todo summation in my report, I get the
Click to expand...
correct sum of $17 for expenses, but $300 for my Amount
Sold.
Any suggestions? Thanks!

.
Click to expand...
One method would be to embed a sub-report for the
expenses, linked by item#. Total the sales on the main
report and the expenses on each sub-report(for item
totals). Overall report totals can use dsum() to get
totals for expenses, sales will work as normal.

Kip
 
Back
Top