Receiving and Completion Process

[JJman]

I am having issues building and defining relationships between four tables (see below) that track batches of items through a receiving and completion process. Any number of users may complete a batch, which creates a one - many relationship between the receiving and completion tables. Each batch is tracked via a batch id field (autonumber as primary key) established in the receiving record table. When a user records completion information for all or part of a batch, they may also need to select one or more other teams/depts that other parts of the batch must be sent to. I am trying to deal with this by adding two additional tables; one that has a list of the teams/depts that pieces may be sent to, and another table that uses a completion id (in common with field in completion table because it's a subset of information) to track which completion records have items sent out and where. Does this table structure seem sound and, if so, I need help with building a query on it that will include all completion records whether corresponding records exist in the "out to" table or not. As it is, my query isn't working, but one thing at a time. Here is a brief overview of the four tables and their linking fields:

tbl_Receiving_Record:
Rec_batch_id (autonumber identifier for each batch)

tbl_Completion_Record:
Rec_batch_id (linked to Receiving Record)
Comp_id ("Comp" = Completion. Autonumber identifier for each completion record)

tbl_Outto_Record:
Completion_id (linked to Completion Record)
Outto_id (Number field linked with autonumber in tbl_outto table below)

tbl_Outto:
Outto_id (autonumber id)
Outto description (text description of team/dept)

Here is my basic SQL for the table relationships:
SELECT
FROM tbl_Receiving_Record INNER JOIN (tbl_completion_record INNER JOIN (tbl_Outto RIGHT JOIN tbl_Outto_Record ON tbl_Outtutto_id = tbl_Outto_Record.Outto_id) ON tbl_completion_record.Comp_id = tbl_Outto_Record.Comp_id) ON tbl_Receiving_Record.rec_batch_id = tbl_completion_record.rec_batch_id;

Thanks for any help you can give!

 

[johnnymac43]

I think I understand your question, but I'm not real sure.

If I do, then your tables and relationships are spot on, although I would suggest some changes to the names you have used.

I assume that you want a query that will show, for each completion, some batch information, some completion information, and some outto information (if any). Based on this assumption, and using my preferred table names, the following SQL should do the trick:

SELECT tblReceiving.Batch, tblCompletion.User, tblOutto.Desc
FROM ((tblReceiving 
    RIGHT JOIN tblCompletion ON tblReceiving.Batchid = tblCompletion.Batchid) 
    LEFT JOIN tblOuttoLink ON tblCompletion.Compid = tblOuttoLink.Compid) 
    LEFT JOIN tblOutto ON tblOuttoLink.Outtoid = tblOutto.outtoid;

Let me know how you go.

Cheers

John

 

[johnnymac43]

Hi JJman

Just wondering how things are going

Cheers

John

 

[JJman]

John,

I apologize I haven't had time to finish it quite yet with other things going on. I'll post again with more info hopefully this week.

Thanks for checking up on it!

 

[JJman]

John,

Some preliminary tests seem to show it's working fine, though there is other information involved that I need to sift through to verify it. I'll post again if I have any issues, but thanks for your help!

 

[johnnymac43]

OK.

Best of luck.

John.