Re: Getting Totals From Two Different Tables In A Query

On Sun, 31 Jul 2005 12:38:42 +0800, "Allen Browne"
<AllenBrowne@xxxxxxxxxxxxxx> 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.
.

Relevant Pages

  • Re: Getting Totals From Two Different Tables In A Query
    ... In the subquery approach, you can use Nz, e.g.: ... the outer join, within the one query. ... >>This handles the case where a client has invoices but no payments. ... >> FROM Payment ...
    (comp.databases.ms-access)
  • Re: Select Last payment date and amount of a group
    ... SELECT T1.Client, PaymentDate, SUMat TotalPaid ... Group By Client) as T3 ... I want a query that will return the LAST payment date and dollar amount ...
    (microsoft.public.access.queries)
  • Re: Configureable Query or otherwise
    ... qrycustomerOutsideTerms, "sending" to your query its argument, the client ... but listing the client only once. ... know if it is possible to build either querries or reports on a customer ... and ArTable shows all invoices with client number name and age that the ...
    (microsoft.public.access.queries)
  • Re: Duplicate entries
    ... Do you have a Clients table and an Invoices Table? ... Assuming you have a clients table, is the client in there more than once. ... you are seeing multiples in a query or in a form based on the query? ... I have developed duplicate duplicate entries. ...
    (microsoft.public.access.queries)
  • Re: How do I find the last record ?
    ... the former you'll need to use a subquery to get the latest payment date ... each client and restrict the query by this, ... The subquery will return the latest payment date for the ...
    (microsoft.public.access.queries)