Report with data from two tables

[khizerk]

Hi,

I have two tables. Sales and Receipts

Sales
Invoice_No
Customer_ID
Date
Amount

Receipts
Receipt_No
Customer_ID
Date
Amount

Now, I want a report which display for each Customer_ID, sales and receipt transactions grouped by date.

The report would look something like this

Customer ID: 1001

Receipt - 0201 - 1/1/12 - 23,000
Sale - S5444 - 1/1/12 - 10,000

Sale - S5477 - 2/1/12 - 12,000

Sale - S5699 - 3/1/12 - 20,000
Receipt - 0210 - 3/1/12 - 24,000

Like that. The transaction would be grouped according to date. How can I achieve this in Access?

Thanks!

[alansidman]

You would do this with an aggregate query. join your two tables on the Customer ID

Here is a link on aggregate queries.

MS Access: Queries scroll down to the group by section

[khizerk]

You would do this with an aggregate query. join your two tables on the Customer ID

Here is a link on aggregate queries.

MS Access: Queries scroll down to the group by section

Hi,

Thanks for the help. I was already trying to do that, but am having issues sorting the data extracted in the format explained above (receipt and sales entries grouped by date)

Here's the SQL

SELECT Customers.Code, Receipts.voucher, Receipts.Date, Receipts.debit, Receipts.credit, Sales.sale_inv, Sales.Date, Sales.Amount
FROM (Customers INNER JOIN Receipts ON Customers.Code = Receipts.code) INNER JOIN Sales ON Customers.Code = Sales.party_code
GROUP BY Customers.Code, Receipts.voucher, Receipts.Date, Receipts.debit, Receipts.credit, Sales.sale_inv, Sales.Date, Sales.Amount;

Problem is, if in the report, I group by receipts.date, then receipts entries get sorted but sales data is shown for that record pertaining to a different date. See below: