Balance Query

[Guest]

I have 2 tables, EnrollmentDetails and Payments; the former lists all details
for another table Enrollment and has a compound Primary Key (EnrollmentID &
ContactID) I want to create a query that sums a value in EnrollemtDetails
(Price) and Subtracts the sum of a value from the joined Payments table. The
tables are joined by EnrollmentID. Whenever I create a simple query to sum
the values in EnrollmentDetails I get accurate values: SELECT EnrollmentID,
SUM(Price) AS Balance
FROM dbo.EnrollmentDetails
GROUP BY EnrollmentID

Yields:
EnrollmentID Balance
9 $600.00
12 $300.00
13 $800.00
14 $600.00

But when I add the joined table to the query view, without even selecting
new output fields, the values from the original fields change:

SELECT dbo.EnrollmentDetails.EnrollmentID,
SUM(dbo.EnrollmentDetails.Price) AS Balance
FROM dbo.EnrollmentDetails INNER JOIN
dbo.Payments ON dbo.EnrollmentDetails.EnrollmentID =
dbo.Payments.EnrollmentID
GROUP BY dbo.EnrollmentDetails.EnrollmentID

Yields:
EnrollmentID Balance
9 $1,200.00
12 $900.00
13 $800.00

What is happening, is it the compound Primary Key of EnrollmentDetails or am
I using the wrong join? Thanks

 

[David Pawloski]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

David Pawloski
Microsoft MVP

 

[Guest]

Was this answer ment for my post, the link doesnt seem to apply?

Steven

 

[David Cox]

I think that you have multiple payments for each student and you are summing
the balance N times..

 

[Guest]

thanks but i do not quite understand your reply.

I think that you have multiple payments for each student and you are summing
the balance N times..

 

[Jeff Boyce]

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Neil Sunderland]

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

It's not really helping if you post this every time. It's a troll, and
you're only encouraging it.

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Complain to the poster's ISP - from the IP address (216.253.202.174)
it's Xspedius Communications Co, so you should send it to
<mailto:[email protected]>

 

[Jeff Boyce]

Neil

I'm of two minds about responding...

On the one hand, I really don't want some poor soul who uses the newsgroups
to inadvertently end up "munged" because of following the advice of the
troll.

On the other hand, I don't want to stimulate/encourage trollish behavior.

I chose to respond to the original posters, and have been responding to
many, since there's no assurance that the OP would have seen any of the
earlier SPAM or responses.

Thanks for the lead on the ISP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Neil

I tried properties of the messages, but didn't spot the IP address. Where
did you find it?

Jeff

 

[Neil Sunderland]

I tried properties of the messages, but didn't spot the IP address. Where
did you find it?

It's the NNTP-Posting-Host header.

I don't use Outlook <spit> so I can't tell you how you find it there,
but you can get at it from Google Groups, for example:
<http://groups.google.co.uk/group/microsoft.public.access.queries/msg/ba6f7c28b6f2e6ed?dmode=source>

 

[John Spencer]

IF a person makes multiple payments then you will get one row for each
payment and in each row there will the price.
Now if you aggregate (sum, groupby, etc) the data, the balance is summed
once for each of the rows you are aggregating.

Save the first query as qSumBalance

SELECT qSumBalance.EnrollmentID
, qSumBalance.Balance AS Balance
, SUM(dbo.Payments.Payment) as AmountPaid
FROM qsumBalance INNER JOIN
dbo.Payments ON qsumBalance.EnrollmentID =
dbo.Payments.EnrollmentID
GROUP BY qsumBalance.EnrollmentID, qSumBalance.Balance

You may need to change the FROM qSumBalance to
SELECT ...
FROM (SELECT EnrollmentID,
SUM(Price) AS Balance
FROM dbo.EnrollmentDetails
GROUP BY EnrollmentID) as qSumBalance
INNER JOIN dbo.Payments
ON qsumBalance.EnrollmentID = dbo.Payments.EnrollmentID
GROUP BY qsumBalance.EnrollmentID, qSumBalance.Balance