Solved Pending Order Query Issues (1 Viewer)

[AnilBagga]

I need to make a pending order report as per format enclosed and need to build a query to combine 3 tables

1. SO Header Table - which has Customer name etc (CustSOHdrTbl)
2. SO Details Table (CustSODtlsTbl) - which has the details of items booked. These 2 tables are linked by a SOHdrID
3. Invoice details (CustInvDtlsTbl) - which has dispatch details of the items sent. This table also has a SOHdrID link.

I started by building the query to link Sr 2 and 3. The query I built is part of the DB enclosed. There are only 2 items in the InvDtls Table but the query has multiple line items for the same part. One row in the Query should be unique for a SOHdrID and the item (ErpFGCode) so that we know the pending order status - Qty booked, sent for each time of the Sales Order (SO)

Each item of the SO can have multiple shipments so for each SOHdrID+ErpFGcode combination, we will have multiple line items in the Invdtls table. this multiple line items will eventually need to be clubbed as one number against the order qty booked so that the pending order can be calculated

Please help. I am new to Access and used to simple queries to build forms. I am dabbling into Queries and reports now and need some guidance

 

[June7]

Tables should be linked on primary and foreign key fields. Your attempted query links on ErpFGCode and ItemCode, neither of which is a designated primary key.

 

[bastanu]

Cross-post with answer https://www.accessforums.net/showthread.php?t=80942