By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Getting Totals From Two Different Tables In A Query

P: n/a
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:

There is a table of payments and a table of charges. Each client has
charges and payments during the month.

I'd like to get the totals of the payments and of the charges for each
client. When I run the following query, I get huge numbers that
appear as if the join is not working correctly.

This gets me the invoice totals by client:
SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This gets me the payment totals by client:
SELECT [Payment].[ClientID] AS Client,
Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE PaymentDate< #10/06/04#
GROUP BY [Payment].[ClientID] ;

However, when I try to get both totals in one query, I get very large
numbers that make it obvious that the join is not working properly.

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
Sum([Payment].[Amount]) AS Payments
FROM Invoice, Payment
WHERE InvoiceDate< #10/06/04#
AND PaymentDate< #10/06/04#
AND Invoice.ClientID = Payment.ClientID
GROUP BY [Invoice].[ClientID] ;

Then I tried to use subquerys to get the totals but that didn't work
out very well, either.

Please help me understand how to write one query to get both correct
totals.

Thank you in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Your 2 existing queries give you the total for all invoices for each client,
and the total for all payments for all clients.

You could now create a 3rd query, using the first 2 as input "tables". In
the upper pane of this query design, if you see no line between the 2
"tables", then drag Query1.ClientID onto Query2.ClientID. Then double-click
the join line: Access pops up a dialog with 3 options. Choose the one that
says:
All records from the invoice query, and any matches from ...
This handles the case where a client has invoices but no payments.

It would also be possible to do it in one query with a SQL statement
something like this:

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
(SELECT Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE (PaymentDate< #10/06/04#)
AND (Payment.ClientID = Invoice.ClientID)) AS Payments
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This kind of thing is called a subquery. More info:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"New Guy" <Ne****@yahoo.com> wrote in message
news:m2********************************@4ax.com...
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:

There is a table of payments and a table of charges. Each client has
charges and payments during the month.

I'd like to get the totals of the payments and of the charges for each
client. When I run the following query, I get huge numbers that
appear as if the join is not working correctly.

This gets me the invoice totals by client:
SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This gets me the payment totals by client:
SELECT [Payment].[ClientID] AS Client,
Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE PaymentDate< #10/06/04#
GROUP BY [Payment].[ClientID] ;

However, when I try to get both totals in one query, I get very large
numbers that make it obvious that the join is not working properly.

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
Sum([Payment].[Amount]) AS Payments
FROM Invoice, Payment
WHERE InvoiceDate< #10/06/04#
AND PaymentDate< #10/06/04#
AND Invoice.ClientID = Payment.ClientID
GROUP BY [Invoice].[ClientID] ;

Then I tried to use subquerys to get the totals but that didn't work
out very well, either.

Please help me understand how to write one query to get both correct
totals.

Thank you in advance.

Nov 13 '05 #2

P: n/a
On Sun, 31 Jul 2005 12:38:42 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Your 2 existing queries give you the total for all invoices for each client,
and the total for all payments for all clients.

You could now create a 3rd query, using the first 2 as input "tables". In
the upper pane of this query design, if you see no line between the 2
"tables", then drag Query1.ClientID onto Query2.ClientID. Then double-click
the join line: Access pops up a dialog with 3 options. Choose the one that
says:
All records from the invoice query, and any matches from ...
This handles the case where a client has invoices but no payments.

It would also be possible to do it in one query with a SQL statement
something like this:

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
(SELECT Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE (PaymentDate< #10/06/04#)
AND (Payment.ClientID = Invoice.ClientID)) AS Payments
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This kind of thing is called a subquery. More info:
http://support.microsoft.com/?id=209066


Very helpful.

Thank you very much.
Nov 13 '05 #3

P: n/a
On Sun, 31 Jul 2005 12:38:42 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Your 2 existing queries give you the total for all invoices for each client,
and the total for all payments for all clients.

You could now create a 3rd query, using the first 2 as input "tables". In
the upper pane of this query design, if you see no line between the 2
"tables", then drag Query1.ClientID onto Query2.ClientID. Then double-click
the join line: Access pops up a dialog with 3 options. Choose the one that
says:
All records from the invoice query, and any matches from ...
This handles the case where a client has invoices but no payments.

It would also be possible to do it in one query with a SQL statement
something like this:

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
(SELECT Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE (PaymentDate< #10/06/04#)
AND (Payment.ClientID = Invoice.ClientID)) AS Payments
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This kind of thing is called a subquery. More info:
http://support.microsoft.com/?id=209066


Allen,

This is working great. I had tried to make a subquery like this
yesterday but I kept getting errors.

Thanks a lot for helping me.

When there is no payment for one of the clients, the query inserts a
blank in the payment field, even though I have a default value of 0
for all the fields except Client. Is it possible to make it insert a
0 instead of a blank.

Thanks again.
Nov 13 '05 #4

P: n/a
In the subquery approach, you can use Nz(), e.g.:
... As Charges
Nz((SELECT ...
AND (Payment.ClientID = Invoice.ClientID)), 0) AS Payments

There's not an easy way to get the Null to show as a zero for fields from
the outer join, within the one query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"New Guy" <Ne****@yahoo.com> wrote in message
news:9e********************************@4ax.com...
On Sun, 31 Jul 2005 12:38:42 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Your 2 existing queries give you the total for all invoices for each
client,
and the total for all payments for all clients.

You could now create a 3rd query, using the first 2 as input "tables". In
the upper pane of this query design, if you see no line between the 2
"tables", then drag Query1.ClientID onto Query2.ClientID. Then
double-click
the join line: Access pops up a dialog with 3 options. Choose the one that
says:
All records from the invoice query, and any matches from ...
This handles the case where a client has invoices but no payments.

It would also be possible to do it in one query with a SQL statement
something like this:

SELECT [Invoice].[ClientID] AS Client,
Sum([Invoice].[InvoiceTotal]) AS Charges,
(SELECT Sum([Payment].[Amount]) AS Payments
FROM Payment
WHERE (PaymentDate< #10/06/04#)
AND (Payment.ClientID = Invoice.ClientID)) AS Payments
FROM Invoice
WHERE InvoiceDate< #10/06/04#
GROUP BY [Invoice].[ClientID] ;

This kind of thing is called a subquery. More info:
http://support.microsoft.com/?id=209066


Allen,

This is working great. I had tried to make a subquery like this
yesterday but I kept getting errors.

Thanks a lot for helping me.

When there is no payment for one of the clients, the query inserts a
blank in the payment field, even though I have a default value of 0
for all the fields except Client. Is it possible to make it insert a
0 instead of a blank.

Thanks again.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.