Relationships and Tables conundrums

[Gobbles]

I have a Purchase Orders table and I have a Purchase Order component in my Database. When we place a Purchase Order, although the relationships are with Purchase Orders table and Suppliers table, we would like to somehow link the Customers Job Card (why the parts are ordered) to the Purchase Order or the CUstomer Order to the Purchase Order so that we can write a query to put in a form so that the user can navigate from one Purchase Order record to the other and see what Purchase Order belongs to what Job Card or Order (either one is okay?) How would i do this. Currently if i link the Job Card to the Purchase Order PROBLEM: it gives a message that the record is required in the Purchase Orders so it doesnt save the Job Card, same goes if I create the Order ID in Purchase Orders. Have you got advice for me on how to bypass this problem or to achieve this form that shows the Purchase Order and Relating Order or Job Card. I have made sure the all tables are "empty" so that there is no clash with records and no records etc.

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=90486

 

[arnelgp]

Crossposted

no problem, nobody yet dared lift their fingers to answer the OP.

 

[arnelgp]

Link it but don't use Inner Join, use either Left or Right Join.

 

[MajP]

The join type is irrelevant. You cannot enforce referential integrity in the relationship window.

 

[Gasman]

no problem, nobody yet dared lift their fingers to answer the OP.

Perhaps not, but people should be aware.
O/P did not even wait to see for any response, just buckshot it onto another site. There may be more.

 

[bob fitz]

The join type is irrelevant. You cannot enforce referential integrity in the relationship window.

Forgive me if I've misunderstood you, but when you create/edit a relationship in the relationship window, you are presented with a dialog box that allows you to set referential integrity.

 

[RogerCooper]

I have a Purchase Orders table and I have a Purchase Order component in my Database. When we place a Purchase Order, although the relationships are with Purchase Orders table and Suppliers table, we would like to somehow link the Customers Job Card (why the parts are ordered) to the Purchase Order or the CUstomer Order to the Purchase Order so that we can write a query to put in a form so that the user can navigate from one Purchase Order record to the other and see what Purchase Order belongs to what Job Card or Order (either one is okay?) How would i do this. Currently if i link the Job Card to the Purchase Order PROBLEM: it gives a message that the record is required in the Purchase Orders so it doesnt save the Job Card, same goes if I create the Order ID in Purchase Orders. Have you got advice for me on how to bypass this problem or to achieve this form that shows the Purchase Order and Relating Order or Job Card. I have made sure the all tables are "empty" so that there is no clash with records and no records etc.

Can a customer job require multiple purchase orders? Can a customer order require multiple jobs?

Also think about the line items. Can a customer order have multiple lines? Can a single line on the customer order require multiple jobs?

What is a customer job? If you are a manufacturer, you may require separate work orders that create intermediate products. Even a wholesaler may need to put customer specific labeling or packaging with a standard product.

 

[MajP]

Forgive me if I've misunderstood you, but when you create/edit a relationship in the relationship window, you are presented with a dialog box that allows you to set referential integrity.

My bad. That was very unclear. Meant to say the user cannot enforce referential integrity if they want it to work as described. So they would have to un select enforce RI.
The user described adding a child record to one of two tables. However i would question the design.