December 11th, 2012, 01:32 PM
Report with data from two tables
I have two tables. Sales and Receipts
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?
December 11th, 2012, 02:38 PM
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
Office 2010, 2007, 2002
If I helped you, then click "give rep" button in the lower left corner.
December 13th, 2012, 11:53 AM
Originally Posted by alansidman
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: