Querying invoice totals problem 2

[rowan1]

I have 3 tables Invoices,details & Clients. I have a query that gives me a list of dates, invoices & amounts spent during a given period. I want to have the query give me a list of the total amount spent by each client during this period. Each client has several invoices
I could specify each client in the criteria and then sum up each invoice amount to give me what I want but with a 100 clients this seems awfully longwinded.¿Is there a way I can print one query to list what each client spent during a given period.

 

[Blorf]

I may not clearly understand the problem, but here goes.

You yave clients in one table, Invoices in another, and invoice details another. The Invoice table has the dates, so you need to include it, but putting the invoice date in your query causes the sum to list everything, even though you only want a summary, so long as the date falls within your date range. One solution:

Instead of including the date, try using a custom field like this:

Include: iif (InvoiceDate >= From_Date And InvoiceDate <= To_Date, &quot;Yes&quot;, &quot;No&quot

As Criteria, specify &quot;Yes&quot;.

This will solve the grouping problem.

Hope I understood your problem, and that this helps.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[JonFer]

Please post the SQL for the query you have already built and also list the fields in your tables.

 

[rowan1]

Blorf.
Yes you understood my problem and I tried what you said with the result that when I open the query I get three prompts; 1.InvoiceDate, 2.From, 3.To. When I run the query I get a list of all the invoices I have issued from the start of the db irrespective of what I enter in the prompts.Any suggestions?

JonFer.
The fields in my table are
Clients: Invoices: Details
Ref InvoiceNº InvoiceNº
Name Clientname Products
Address Quantity
Date Price
I then have a query, invoice sales which I then query as follows
Field: Date Name Total
Table: Invoicesales InvoiceSales Invoicesales
Total: GroupBy GroupBy Sum

In the query of the date I put the Period I want and it is then I am stumped as how to achieve a summary of all the clients sales total for that period.

Thanks for your time so far.

 

[Blorf]

Can you send the Sql of your query?

ChaZ

Ascii dumb question, get a dumb Ansi

 

[rowan1]

My SQL.
SELECT[InSELECT [Invoice sales ].InvoiceNº, Invoices.Date, Invoices.Clientname, Sum([Invoice sales].Total) AS SumOfTotal, IIf([InvoicesDate]>=[From_Date] And [InvoicesDate]>=[To_Date],&quot;Yes&quot;,&quot;No&quot AS Expr1
FROM [Invoice sales] INNER JOIN Facturas ON [Invoice sales].InvoiceNº = Invoices.InvoiceNº
GROUP BY [Invoice sales].InvoiceNº, Invoices.Date, Invoices.ClientName, IIf([InvoicesDate]>=[From_Date] And [InvoicesDate]>=[To_Date],&quot;Yes&quot;,&quot;No&quot, IIf([InvoicesDate]>=[From_Date] And [InvoicesDate]>=[To_Date],&quot;Yes&quot;,&quot;No&quot;

Hope this helps. Thanks

 

[JonFer]

This will total the invoice amounts with dates in the range you specify.

SELECT [Invoice sales].InvoiceNo, Invoices.Date, Invoices.Clientname, Sum([Invoice sales].Total) AS SumOfTotal
FROM [Invoice sales] INNER JOIN Invoices ON [Invoice sales].InvoiceNo = Invoices.InvoiceNo
Where Invoices.Date Between [From_Date] And [To_Date]
GROUP BY [Invoice sales].InvoiceNo, Invoices.Date, Invoices.ClientName;

 

[rowan1]

JonFer & Blorf

I am finding this a great help so far but I would like to go further and in my final query I do not need the invoiceNº and date but I need a total sum of all invoices for each client.

Thanks again for your help

 

[JonFer]

Remove the unwanted fields from the query's SELECT and GROUP BY list.

 

[rowan1]

Thanks for the help.The last gave me exactly what I wanted