Results 1 to 6 of 6
  1. #1
    Farooq is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    4

    Joining data from different tables based on date field in one query

    Hi

    Working on an inventory database. I have Four different tables containing Opening, Receiving, Issuing and Returns of items with different dates. I wish to join them in a single query which contain only transaction type and transaction date (sort on date).

    I am new to MS Access, was able to do this in MS Excel but database is getting bigger and slower. Would appreciate any help and thanks in advance.

    Table1 Item.Items Opening.Quantity Opening.OpeningDate
    Table2 Item.Items Receiving.Quantity Receiving.RecvdDate
    Table3 Item.Items Issuing.Quantity Issuing.IssueDate
    Table4 Item.Items Returns.Quantity Returns.RetDate

    Required Query: Sort on Transaction.Date

    Query1 Item.Items Transaction.Type (Open, Receiving, Issue, Returns) Transaction.Date(OpeningDate, RecvdDate, IssueDate, RetDate)



    Kindly also guide if images of the required table and queries will be helpful in understanding and answering my question.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,842
    Quote Originally Posted by Farooq View Post
    Kindly also guide if images of the required table and queries will be helpful in understanding and answering my question.
    Wouldn't do any harm. Posting the db might be best.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,320
    My first thoughts are that your structure for database is incorrect, but I don't know the details of your business.
    Typically you would have a Products/Items table and a Transaction table.

    Incoming Product transactions are positive, outgoing/sales would be negative. Do the calculation of current QtyOnHand based on

    QtyOnHand = lastPhysicalCount +(incomingTransItems - OutgoingTransItems)

    see: Allen Browne AppInventory for more info

  4. #4
    Farooq is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    4
    Sample Database.zip Thanks for the reply. I have attached a sample database and an Excel sheet for what I am doing and what is my requirement. Kindly share if need more clarification.

  5. #5
    Farooq is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    4
    @ orange. Thanks for the reply and link to a valuable resource. I have items table as you can see in my reply to Bob. I have four transactions table, which I wish to join in a single transaction query, then I will be able to apply above formula. I think.

  6. #6
    Farooq is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    4
    Thanks Orange. Thanks Bob. After days of searching found my answer in a simple statement " Union is for vertical join". See my code here and I got the query joining four tables based on date criteria.

    Code:
    SELECT Opening.[Item_Code], Opening.[Trans_Date], Opening.[Qty]
    FROM Opening
    Order by [Item_Code];
    UNION
    SELECT GRN_Details.[Item Code], GRN_Details.[Trans_Date], GRN_Details.[Quantity]
    FROM GRN_Details;
    UNION
    SELECT MIR_Details.[ItemCode], MIR_Details.[Trans_Date], MIR_Details.[Qty]
    FROM MIR_Details;
    UNION SELECT Return_Details.[ItemCode], Return_Details.[Trans_Date], Return_Details.[Qty]
    FROM Return_Details;


    Link: https://support.microsoft.com/en-us/...0-ad0a75541c6e

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

Similar Threads

  1. Joining Tables Based on Date
    By kestefon in forum Access
    Replies: 5
    Last Post: 02-05-2014, 04:43 PM
  2. Replies: 1
    Last Post: 07-23-2013, 01:39 AM
  3. Joining Tables where 1 field has an extra character
    By smoothlarryhughes in forum Queries
    Replies: 5
    Last Post: 12-13-2012, 02:53 PM
  4. Joining 2 Tables based on a Common Relation to Another
    By StudentTeacher in forum Programming
    Replies: 5
    Last Post: 07-26-2011, 07:23 AM
  5. Changing tables based on field date
    By dssrun in forum Programming
    Replies: 6
    Last Post: 07-10-2011, 10:17 AM

Tags for this Thread

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 - Senior Forums