Results 1 to 4 of 4
  1. #1
    zaffar_mughal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    6

    Combining date columns from differnet tables and retrieve data

    Hello All,

    First of all let me say Hi to all of you, this is my first post/request to solve the issue. I tried what i could but no luck so far.

    I'm trying to retrieve information from 3 tables (Order, Receive and Issue) to one table as Inventory Table/Query. it looks like as follows
    Order Table:
    OrderID
    ProductID OrderDate OrderQTY
    1 1 3/1/2012 30
    2 1 4/1/2012 10
    3 2 4/1/2012 20

    Receive Tabe:
    ReceiveID ProductID ReceiveDate ReceiveQTY
    1 1 3/3/2012 30
    2 1 4/3/2012 10
    3 2 4/2/2012 15

    Issue Table:
    IssuedID ProductID
    IssuedDate IssueQTY
    1 1 3/5/2012 20
    2 1 4/6/2012 10
    3 2 4/5/2012 5



    I'm looking for Inventory table/query which will give all of the data per date, which is like the following

    Inventory:
    Date ProductID OrderQTY ReceiveQTY IssueQTY
    3/1/2012 1 30
    3/3/2012 1 30
    3/5/2012 1 20
    4/1/2012 1 10
    4/1/2012 2 20
    4/2/2012 2 15
    4/3/2012 1 10
    4/5/2012 2 5
    4/6/2012 1 10

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    1)
    a) Do a UNION query of the Dates from the 3 tables.
    b) Then use this UNION query on the Left Side of a Left Join with the 3 tables that you have.

    2) Perhaps, the Receive Table & Issue Table could be one table instead of 2 tables.

    Edit : The Product code also needs to be handled in the join. I have posted on the fly.

    Thanks

  3. #3
    zaffar_mughal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    6
    Awesome, worked like a charm. Thanks alot for your help.

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Actually, my reply was all messed up, with the hints scattered all around.
    Still, you managed to work it out. That, really, is awesome.
    Glad you found it helpful.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-29-2012, 09:21 PM
  2. Replies: 3
    Last Post: 02-08-2010, 09:00 AM
  3. Replies: 4
    Last Post: 12-16-2009, 07:31 AM
  4. Combining columns into rows
    By steeveepee33 in forum Queries
    Replies: 5
    Last Post: 04-30-2009, 09:18 PM
  5. Replies: 1
    Last Post: 06-21-2007, 01:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums