Need a sales receipt (report) from MS Access 2007 with multiple items and multiple payments.

I have a MS Access database in 2007 that controls inventory, clients, and sales.  I need to be able to print a sales receipt (report) for a sale that contains multiple items and multiple payments.  I have the following tables: Inventory, Clients, Sales, SalesItem, Payments.  The sale table contains basic sale information and is linked to both the SalesItem table and Payment table.  I have a report based on query, but my problem is that I'm not sure how to get the report to populate correctly for an sale with multiple items and multiple payments.  I can get the report to detail the items of a sale and a sum of payments, but I can't get a detail or both.
Who is Participating?
GrahamMandenoConnect With a Mentor Commented:
Create a header/detail/footer report based on a query of
Clients < Sales < SaleItems > Inventory.

This is justlike a standard invoice, with Client and Sale information in the header, SalesItem info in the detail, and a total in the footer.

Then, in the footer, add a subreport based on Payments, linked to the main report on SaleID.  The subreport lists the dates and amounts of the payments, with a total in a textbox in the subreport footer (which can be hidden if you prefer).

Then, the balance owing can be calculated on the main report like this:
=txtSaleTotal - Nz( [sbrPayments].[Report]![txtPaymentsTotal], 0 )

Graham Mandeno [Access MVP]
Well depending if you want to have an overview of the Sales vs Payments of a Client.
Do you want this like a summary level of all Sales vs Payments or inlcuding OrderID, to see for each SalesTotal the PaymentTotal?

Seperate Query1;
ClientID, SalesID, SumSales

Seperate Query2, linked to SalesID;
ClientID, SalesID, SumPayment

I wiould create a Query3, linked on a LEFT JOIN with Query 1, Query 2, with having;
ClientID, ClientName, SalesID, SumSales, SumPayment

You can create a Report with as Header Seaction the ClientID and Client Name, in the Details section you will place the SalesID with SalesTotal, SumPayment, then in the Footer Section the Grand Total Calculations.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.