Re: Getting Totals From Two Different Tables In A Query

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" <NewGuy@xxxxxxxxx> wrote in message
news:m2joe15hfi1jfe1snocvo780g9sjn057ls@xxxxxxxxxx
> 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.


.

Relevant Pages

  • Re: report group subtotals incorrect
    ... there are multiple payments of the invoice, ... This text box accumlates the total for the customer over their invoices, ... An alternative idea would be to use a subreport for the payments. ... > tehn totals per group. ...
    (microsoft.public.access.queries)
  • Re: create totals based on a field values...
    ... In general an invoice is no more final than ... form or report calculations to display those values ... Payments table. ... >doesn't change therefore the totals don't change... ...
    (microsoft.public.access.queries)
  • Re: How to prevent duplicates?
    ... Orders to Payments should be a one to many relationship... ... On your main report each Invoice record should be derived from a Totals ... > Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN (SIN INNER JOIN ...
    (microsoft.public.access.reports)
  • Re: create totals based on a field values...
    ... Once my invoice is created, ... totalinvoice amount and build from there to accept payments so that I can ... Since a Totals type query like I ...
    (microsoft.public.access.queries)
  • Re: how to connect the receipt to the purchase items?
    ... Client table ... Invoice table ... Now you want to handle payments received. ... a double entry accounting system is required for ...
    (comp.databases.ms-access)