Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Report with data from two tables

Results 1 to 3 of 3
Share This Thread →
  1. #1
    khizerk is offline Registered User
    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
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    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.

  3. #3
    khizerk is offline Registered User
    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by alansidman View Post
    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:


Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates