Grouped Totals Report Question

[Ash Skilton]

Hi was hoping someone might be able to explain how to do something for me.

I have a table with:

Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid

Example of data:

AK001 | A Brown | Blue Car | £1000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Red Car | £2000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Yellow Car | £500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown | Black Car | £1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith | Blue Car | £1000 | 12/4/2013 | Yes | 25/4/2013

I want to create a report from this table that outputs as:

Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid

Example of report from Example Data:

AK001 | A Brown | £3500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown | £1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith | £1000 | 12/4/2013 | Yes | 25/4/2013

Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?

Thanks,
Ash

 

[pr2-eugin]

Hello Ash Skilton, Create a Query something along the lines of..

SELECT InvoiceNumber, CustomerName, Sum(TotalPrice) AS SumOfTotalPrice, InvoiceDate, Paid, DatePaid
FROM carTableName
GROUP BY InvoiceNumber, CustomerName, InvoiceDate, Paid, DatePaid;

And set this as the Reports Recordsource..

 

[Ash Skilton]

Resurrecting my question.

The above worked perfectly at first but have encountered a small problem where if a customer has an item on their invoice valued at £0 (which we do have every now and again) it doesn't merge the £0 item with the others causing there to be two lines for the invoice on the report, one at £0 and one with the total of the rest of the items.

Is there a way to stop this happening so it all remains in one line?