Setting up table relationships for multiple chemicals that have multiple containers arrival dates

[Kirsten A]

Hope I’m putting this in the right forum?


How should I set up the tables for this senario? I have many chemicals that will have many arrival dates (ie each chemical has multiple dates and I have many chemicals) I have tried doing a joining table but that leaves my arrival dates blowing in the wind and access is telling me I need to link my arrival dates to my chemicals but that would be a many to many relationship.

[orange]

Can you give us an example of receiving a certain chemical, the (multiple) dates involved, and your use of that chemical elsewhere and how the dates are relevant?
You are knowledgeable of the details and we are not. We need more context to respond.

[Kirsten A]

Hi, Thanks for replying. The chemical would be

1,4-Dithioerythritol DTE

For example which arrived 01/08/16 and 01/08/17. I think this is such an obvious thing that you may think it's more of a problem than it is. I have a table with my chemicals and a table with my arrival dates and i'm not sure how to join them. Sorry really not having a good tech day today.

[John_G]

All you need to do is add the chemical name (or however you uniquely identify each chemical) to the arrival dates table. You would then join the two tables on the chemical ID, creating a one-to-many relationship - each chemical has many arrival dates.

[Kirsten A]

Thanks, I did that initially and kept getting an error. No idea why, but I thought it'd be some thing simple. Back to the training vids I think.

[Kirsten A]

Ok I have in my arrival tb- chemicaliD (as a Foreign key) and I want to link it to chemicals ID (primary key) my chemicals table? Because when I do that I'm getting a "data in arrival tb violates referential integrity rules".

[John_G]

That means that the ariival table has one or more valies in the chemical_ID field that have no corresponding record in the Chemicals table. For example, if in the arrivals table there is a record (or more than 1) with chemical_ID = 10, then there must also be a record in the Chemical_ID = 10. That is what referential integrity means - it protects you from having records in the Arrival table which refer to non-existant Chemicals.

After adding the Chemical_ID field to the Arrival table, did you populate it with the Chemical_ID values? You have to do that before you define the relationship between the tables.

[orange]

Kirsten,

What exactly do you mean when you receive many Chemicals on Many Dates?
Do you have "batches" or "lot numbers" for each Chemical received?
What do you do (how do you use) arrival dates and chemicals?

A primary key is a special relational database table column (or combination of columns) designated to uniquely
identify all records in that table. A primary key's main features are: It must contain a unique value for each row of data.
It cannot contain null values.

We need to understand your set up in order to offer more focused advice.
Good luck.

[Kirsten A]

Thanks John, I'll look at the data in the table. I think I understand the concept lol.

Orange, I have an excel spreadsheet with all our chemicals listed + their arrival dates as well as other details but it's not very searchable so I want to transfer that info to access. Just starting to learn. I want to have my chemicals linked to multiple arrival dates as well as courses ect. All the other table relationships seem to be working fine, it was just the arrival dates that had the problem. I will do as John sugested and look at my data and see if I can fix it.

Thanks for answering my newbe questions.

[Kirsten A]

Ok I've done that and have set the relationship but it's set itself as a 1 to 1 not a 1 to many and when I tried to add a second date to a chem it said I couldn't have duplicate records. Would the chem id + a unique date not be differnet?

[Kirsten A]

Ok I've managed to sort the problem, thank you so much for your advice. I'm sure I will be back with more dumb questions. Thanks you for your patience.

[orange]

How about telling or showing readers your solution?
Others, newbies especially, may get insight from your solution. The key to the forum posts is to provide assistance/advice -so posting your solution may help someone.
Good luck.

[Kirsten A]

Sure It was the data in the tables that was the problem, so I sorted that out and put values where they were needed. I then did an arrival Date table with a primary key which I joined to another table (called Arrivaldate/chemiclasTb) as a foreign key. In that table I have my chemicals foreign key from my chemicals table.
I’m hoping that will work when I come to retrieve the info and when I update the arrivals table it will update the join table and will know which chemical goes with which date.