Join Key error

[roger111]

Hi, I'm new to access and a bit slow. I have an error message saying "the current field must match the join key "?" in the table that serves the one side of the one-to-many relationship enter a record in the one side table with the desired key value and then make the entry with the desired join key in the many only table.



I have a screen shot of the error and my table relationships.
Thanks
Roger

[orange]

You say you are new to Access, but have you done any database design activity with other products?
You call your table OrderTracker but your PK is InvoiceNumber?
Your relationship diagram shows a CustomerDetails table with Company as PK, and a Customer may have many OrderTracker and in Orderliine you carry the Company value. Since the Orderline is identified via the OrderTracker, that identifies the Company, there is no need to carry Company into the Orderline.

It looks to me that you have spent some time designing your tables with some intent to have a normalized set up. I think your design needs a little work, but the amount and specifics really depend on your business facts and requirements. It also seems you have attributes/fields in your tables that may relate to another (hidden) entity.

With database you must add a parent record before you can add a child record which may be the heart of your issue. And conversely, you must delete child records before deleting the parent.(to prevent orphans) You should also be aware that multi-table queries are often not updateable (read only).

Usually in Access you use a Form/sub Form construct to deal with Parent/Child records. Also, it is common to use a form to modify/manipulate data in a table. User interaction with a database is through Forms which act much like a window into the database.

It would be helpful to you and readers if you could describe your business in simple plain English. It gives a context for understanding what the business is about and how the things (tables/entities) relate to one another.

My recommendation for database design is to start with a 30,000 foot description of a day in the business. Then gradually add details to clarify the entities and their relationships (in plain language - no jargon). Then work from the description to create a draft model (pencil and paper is fine); take some sample data and a few test business scenarios; and validate/adjust the model until it supports the test scenarios. Reconcile every anomaly and test again. This will give you a blue print for your database structure that you have tested and accepted.

Here are some links that may be helpful.

Good luck with your project.