#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Report with data from two tables


    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!
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    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
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by 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
    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:

Similar Threads

  1. Replies: 5
    Last Post: September 19th, 2012, 01:35 PM
  2. Report on Multable Tables Displays Erroneous Data
    By cduval in forum Microsoft Access Help
    Replies: 4
    Last Post: May 7th, 2012, 10:38 AM
  3. Replies: 4
    Last Post: February 7th, 2011, 09:29 PM
  4. Moving data from 2 related tables to 2 new tables as an archive
    By JJKramer in forum Microsoft Access Help
    Replies: 1
    Last Post: April 3rd, 2007, 08:27 PM
  5. Add data to two tables.......
    By jediwizzo in forum SQL Development
    Replies: 5
    Last Post: October 8th, 2003, 08:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo