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.

 

[Pat Hartman]

Welcome.

1. The error is likely occurring because Access, not so helpfully, assigns 0 as the default for long integers. Most long integers are autonumbers so a far better default (and was the default in previous versions of Access) is null. Remove the zero as the default.
2. Roger asks several rational questions. The answers will help you to determine how to link the PO's to the Jobs. It is probably too much of a hassle to link at the line item level so, maybe don't bother linking them at all and add a popup that just shows all the open orders for the Job's customer. But it really depends if their practice is to have one PO that orders parts for multiple jobs. So, answer Roger's questions and we will discuss the options with you.

FYI, as the others have indicated, it is poor practice to simply blast the internet with the same question. Think of all the expert time you are wasting with this tactic. You may think it helps you but the reality of it is that it doesn't. Once we figure out that you think we just all work for you for free so our time is meaningless, we stop even attempting to help you. Don't be that person. When you post a question, you probably need to give it at least a few hours to see if you get a response. This website has pretty wide coverage since we have a large concentration of posters in the US and Europe and a bunch also in Asia and a growing number from Africa so there are people here almost 24/7.

 

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