asked on

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.

ASKER CERTIFIED SOLUTION

Graham Mandeno

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

danishani

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.

HTH,
Daniel