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

[Farooq]

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.

[Bob Fitz]

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.

[orange]

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

[Farooq]

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.

[Farooq]

@ 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.

[Farooq]

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