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

 

[Guest]

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).

 

[Fahd]

SELECT Sum(PATSERVICES.PATCHRG) AS SumOfPATCHRG, Sum(PATSERVICES.INSCHRG) AS
SumOfINSCHRG, Sum(PMT2SERV.AMOUNT) AS Payment
FROM PATSERVICES LEFT JOIN PMT2SERV ON PATSERVICES.PATSERVICESKEY =
PMT2SERV.PATSERVICESKEY
GROUP BY 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#));

This is what I used to sum.
Karl, can you be more specific please? the two tables are big, what is it
that you're looking for?


----- Original Message -----
From: "Marshall Barton" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Monday, May 21, 2007 19:20
Subject: Re: Get the Correct Total

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).

 

[Guest]

Post the records from the tables that are used in getting these results.
--
KARL DEWEY
Build a little - Test a little

SELECT Sum(PATSERVICES.PATCHRG) AS SumOfPATCHRG, Sum(PATSERVICES.INSCHRG) AS
SumOfINSCHRG, Sum(PMT2SERV.AMOUNT) AS Payment
FROM PATSERVICES LEFT JOIN PMT2SERV ON PATSERVICES.PATSERVICESKEY =
PMT2SERV.PATSERVICESKEY
GROUP BY 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#));

This is what I used to sum.
Karl, can you be more specific please? the two tables are big, what is it
that you're looking for?


----- Original Message -----
From: "Marshall Barton" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Monday, May 21, 2007 19:20
Subject: Re: Get the Correct Total

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).

 

[Marshall Barton]

You need to use two queries because there are two different
Group By clauses.

I can't tell for sure from your sample output, but I suspect
that you are grouping by the wrong fields. Shouldn't the
grouping be on the PATSERVICESKEY field? If so, I think you
want something more like this air code:

Qry1:
SELECT PATSERVICESKEY,
Sum(PATCHRG) AS SumOfPATCHRG,
Sum(INSCHRG) AS SumOfINSCHRG
FROM PATSERVICES
GROUP BY PATSERVICESKEY,
PATSERVICES.FROM, PATSERVICES.TO
WHERE (PATSERVICES.FROM Between #5/21/2007# And #5/21/2007#)
AND (PATSERVICES.TO Between #5/21/2007# And #5/21/2007#)

Qry2:
SELECT Qry1.PATSERVICESKEY,
SumOfPATCHRG,
SumOfINSCHRG,
Sum(PMT2SERV.AMOUNT) AS Payment
FROM Qry1 LEFT JOIN PMT2SERV
ON Qry1.PATSERVICESKEY = PMT2SERV.PATSERVICESKEY
GROUP BY Qry1.PATSERVICESKEY,
SumOfPATCHRG,
SumOfINSCHRG,
PATSERVICES.FROM, PATSERVICES.TO
WHERE (PATSERVICES.FROM Between #5/21/2007# And #5/21/2007#)
AND (PATSERVICES.TO Between #5/21/2007# And #5/21/2007#)
--
Marsh
MVP [MS Access]

SELECT Sum(PATSERVICES.PATCHRG) AS SumOfPATCHRG, Sum(PATSERVICES.INSCHRG) AS
SumOfINSCHRG, Sum(PMT2SERV.AMOUNT) AS Payment
FROM PATSERVICES LEFT JOIN PMT2SERV ON PATSERVICES.PATSERVICESKEY =
PMT2SERV.PATSERVICESKEY
GROUP BY 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#));

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).