Query related to 2 tables

[Anil Bagga]

I need to make a pending order report 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 Test12June2020.zipguidance

[orange]

Start with a clear description of the business you intend to support with a database. Beginning with an overview and gradually add details to tie the pieces together. Get a working model on paper before jumping too quickly into a physical database.
Gather/create some test data and test scenarios and test/vet your model. Adjust as necessary and retest until you are satisfied that the "logic" and testing meets your requirement. You now have a blueprint for your database.

See the database planning and design link in my signature for more related info.
Good luck with your project.

[Anil Bagga]

Thank you the lovely articles.

I did the basic modelling and have been on this project for a month. I do not have exposure to any VBA and have been helped by this forum in a big way. Data is now under compilation. The basic structure of the report is also ready as enclosed . I need some guidance on combining tables in a query to get the desired results.

[Gicu]

Have a look at the updated query, you should be able to build from there.

Cheers,

[Gicu]

Cross-post https://www.access-programmers.co.uk.../#post-1701846

[Anil Bagga]

I built on the query and found some issues.

Let me explain the problem

There are 2 queries I built

1. PendingOrderqry - this is the master query where I want to add data from the query below. The records are 193
2. Dispatchqry - this has the sum total of parts sent of each SO (SOHdrID). This has 16 records

Basically for each combination of SOHdrID and ItemCode in Qry Sr 1, I want to pull data from Qry in Sr 2 for the same SOHdrID and ItemCode combination and add it against the relevant record

I added the 'SumofQtySent' of Qry 2 in Qry 1 after linking the SOHdrID for a relationship. The no of items immediately reduces to 83 as only those Header ID's are visible which are available in Qry no 2. Is the solution a Dlookup function in Qry no 1 to lookup the value 'SumofQtySent' for each combination of SOHdrID and ItemCode? If yes what will be syntax?

[Gicu]

Here you go, just use an outer join on both fields.

Cheers,

[Anil Bagga]

Thank you. I will develop this further

[Anil Bagga]

Vlad,

I have developed the query further as per need. For better understanding I tried to replicate this query but had problems. The relationships were all the same as the DB you sent. There is a column 'Expr1000' in the layout of the query which is not visible in the Design view. What is this and is it the key to the solution?
The next step for me is to Use the query data , combine it with data from another table, add some other data through a form and create a new table. I tried many things but could not find a way. Just suggest the way. I want to do it myself

The table (CustPendingOrdertbl) format is added in the DB enclosed.

[Gicu]

Usually Expr1000 fields are duplicates (they have been added twice to the query so Access automatically renames one of them as all query field names must be unique) and can be removed. I couldn't see one in the attached db. Sorry but I don't really get what you're asking. What have you tried and didn't work? What exactly are you trying to do (add what data with what form to which table and how it relates to the pending orders query)?

Cheers,

[Anil Bagga]

Vlad,

1. The Column Expr1000 was hidden. You can see it when you run the Query 'PendingOrderqry' this field is not visible in the Design view. Why is this? You can see it now in the DB enclosed. WHat should I do?

2. The DB has a table called "CustPendingOrdertbl'. This represents the report I need. If you see the table in Design view, you will see that majority of the fields are from the query created, but some are to be added by the User and this data needs to be stored before making the report. The question is how can I dynamically link the query to this table and also have the ability to add data to the table?

The idea is that when the User opens a form linked to the "CustPendingOrdertbl' the data in the table linked to the query is updated (maybe by a Command button) and the User linked fields would carry the data from the table as saved earlier. The user then updates these User linked fields and runs the report linked to the table.

I hope I have been able to clarify the issue!

[Gicu]

Thanks, a lot clearer now!

For the first part you have the HeaderID twice in the query (the very first row then towards the end when it is relabeled Expr1000 when you apply the sort on it). Move the sort to the first instance then delete the second as it is not needed. Alternatively if you want a custom sort order uncheck the Show check box for the second instance where you sort.

Now for the CustPendingOrdertbl you should remove all the fields coming from the query and only leave the primary key (autonumber) the foreign key(s) (SOHdrID/ItemCode) and the user input fields. Then you create a Form bound to the PendingOrderqry query (as the recordsource) with a subform bound to CustPendingOrdertbl and have them linked on the pair of keys (SOHdrID/ItemCode). Now as you navigate the main form based on the query the subform should display the user input that already happened for that record or a new record to be entered. Finally in the record source for the report calculate the two calculated fields (OrderKg/PendingKg) and display them on the report.

Cheers,

[Anil Bagga]

Thanks.

The built the main form, from the query, added the subform linked to the table and checked the data validation. It is working fine

To build the report, i added the Query and the table in the QBE grid and related them on SOHdrID and ItemCode (between the query and the table). I get just the first record of the Query. If I remove the relationship of item code and have only one relationship with SOHdrID, I get all the Itemcodes the first SOHdrID ONLY.

Of course if I remove both the relationships, I get all the 193 records of the query in the report!

I should be able to get all records of the query! This is the same problem I was having earlier when building the query. Where am I going wrong?

I have enclosed the DB

Please help!

[Gicu]

Here you go, same idea as in the previous query, I changed the joins from equi-joins (that only show matching records in the query and table and were returning only one record because that is what you entered in the table) to outer joins to show all the records in the query and the matching records from the table.

Cheers,

[Anil Bagga]

Thanks Vlad. I missed the type of joins in the first communication. I read about it now and checked the properties. Thanks once again