Hi!! I Need some help extracting data from a table for a report.
My table structure is as follows:
Cust-Code Inv-No Inv-Date Details Rent-pm Months Amount
282 133 01.03.22 Rent 150.00 5 750.00
282 133 01.03.22 VAT @ 5% 37.50
357 134 04.03.22 Rent 500.00 4 2000.00
357 134 04.03.22 VAT @ 5% 100.00
405 135 06.03.22 Rent 200.00 6 1200.00
405 135 06.03.22 VAT @ 0% 0.00
There will always be 2 entries per Cust-Code in the table per Invoice.
1 for RENT and the other for VAT (whether 0% or 5%).
Some Customers are billed VAT @ 5%
Some Customers are billed VAT @ 0%
I need a report as below only for Invoices having VAT @ 5%
Invoices having VAT @ 0% should be ignored
Report should have only 1 line per Invoice as below
Cust-Code, Invoice-Num, Rent-Amt, VAT-Amt, Total-Amt(Rent+VAT)
282 133 750.00 37.50 787.50
357 134 2000.00 100.00 2100.00
Inv# 135 has been omitted from the above report as VAT is at 0%.
Any help and pointers would be greatly appreciated.
I'm using Access 2003.