Get the Correct Total

[Fahd]

I have two tables with 1 to many relationship
[PatServices] 1one-many [Pmt2Serv]
[PatServices] holds all the charges and [Pmt2Serv] holds all the payments.
Here is the sql statement:

SELECT PATSERVICES.PATCHRG, PATSERVICES.INSCHRG, Sum(PMT2SERV.AMOUNT) AS
[Payment]
FROM PATSERVICES LEFT JOIN PMT2SERV ON PATSERVICES.PATSERVICESKEY =
PMT2SERV.PATSERVICESKEY
GROUP BY PATSERVICES.PATCHRG, PATSERVICES.INSCHRG, PATSERVICES.FROM,
PATSERVICES.TO
HAVING (((PATSERVICES.FROM) Between #5/21/2007# And #5/21/2007#) AND
((PATSERVICES.TO) Between #5/21/2007# And #5/21/2007#));

the output is:
PATCHRG INSCHRG Payment
$10.00 $15.00
$20.00 $80.00 $100.00
$50.00 $20.00

which is fine, but when I sum up the charges, I get this:
SumOfPATCHRG SumOfINSCHRG Payment
$120.00 $275.00 $100.00

how can I go by getting the right total?
which would be:
SumOfPATCHRG SumOfINSCHRG Payment
$80.00 $115.00 $100.00

thanks

 

[KARL DEWEY]

Post the data from the two tables.

 

[Marshall Barton]

I have two tables with 1 to many relationship
[PatServices] 1one-many [Pmt2Serv]
[PatServices] holds all the charges and [Pmt2Serv] holds all the payments.
Here is the sql statement:

SELECT PATSERVICES.PATCHRG, PATSERVICES.INSCHRG, Sum(PMT2SERV.AMOUNT) AS
[Payment]
FROM PATSERVICES LEFT JOIN PMT2SERV ON PATSERVICES.PATSERVICESKEY =
PMT2SERV.PATSERVICESKEY
GROUP BY PATSERVICES.PATCHRG, PATSERVICES.INSCHRG, PATSERVICES.FROM,
PATSERVICES.TO
HAVING (((PATSERVICES.FROM) Between #5/21/2007# And #5/21/2007#) AND
((PATSERVICES.TO) Between #5/21/2007# And #5/21/2007#));

the output is:
PATCHRG INSCHRG Payment
$10.00 $15.00
$20.00 $80.00 $100.00
$50.00 $20.00

which is fine, but when I sum up the charges, I get this:
SumOfPATCHRG SumOfINSCHRG Payment
$120.00 $275.00 $100.00

how can I go by getting the right total?
which would be:
SumOfPATCHRG SumOfINSCHRG Payment
$80.00 $115.00 $100.00

Your results imply that there are 3 payments $20 PATCHRG
item.

You need to tell us how you are summing the charges. If you
are changing the query to something else, that might be
easily explained by a different Group By (because the Join
has a record for each payment).