Linking Tables for Queries, Forms and Reports

[OlBill]

If you want to link assetid to the many table, then it would be the pk of the one table?, in this case assetid?

Thanks. So AssetTBL-The Primary Key Should be AssetID and that will be the One
SampleTBL will need an AssetID field and that will be the Many, but not the PK?

[Welshgasman]

No it will be the FK, that is how they will be linked.
https://m.youtube.com/results?sp=mAE...ss+one+to+many

[OlBill]

How about now?

[Welshgasman]

That looks a lot better.

However I would not leave a PK as ID, especially in more than one table, so perhaps SampleID?
Same with AssetID in Sample table, perhaps call that AssetIDFK.

Reason being Access gets confused as it will not know which one you are talking about in which table unless tou qualify the field name with the table name. So I prefer to give them unique names, especially as that is what I did not do when I first started using Access. Also the FK suffix immediately tells you what it is?
Right now you know that, but 6 months down the line, it will be forgotten.

Now you would have a mainform for your Asset table and a subform for the Sample table, linked by AssetID.

[OlBill]

Change the name of the PK on the Assettbl?

[Welshgasman]

Change the name of the PK on the Assettbl?

No leave that alone, change the name of assetid in the sample table to assetidfk.
Use case, but I am typing on a tablet, so just using lowercase.

[OlBill]

No leave that alone, change the name of assetid in the sample table to assetidfk.
Use case, but I am typing on a tablet, so just using lowercase.

On SampleTBL - Does AssetIDFK become the primary key for that table?

[Welshgasman]

No.
It has FK in it for a reason. To indicate it is a Foreign Key?
PK for Sample table would likely be SampleID ?

[OlBill]

No.
It has FK in it for a reason. To indicate it is a Foreign Key?
PK for Sample table would likely be SampleID ?

Ok, AssetTBL - AssetID is now PK
On SampleTBL - PK is ID (autonumber), FK is AssetIDFK. I have a LeadSampleID and an AsbestosSample ID in my SampleTBL. The two ID numbers are different. Lead will begin with PB and Asbestos ASB. And they can both apply to the AssetID separately.

In other words, I may have one AsstID with 10 LeadSampleIDs and 5 AsbestosSampleIDs, all different on different dates with different results. The only thing they have in common is the AssetID. I need to tie everything back to the AssetID.

[Welshgasman]

Personally I would not have any more than one field with a name of ID. Preferably none. SampleID indicates where it is from and what it is. All my PKs are Autonumber and end with ID, so I know they are what they are.

[OlBill]

That's what they're called, I didn't name them.

[Welshgasman]

That's what they're called, I didn't name them.

When you create a table ACcess names the autonumber ID. It is up to you to change it to something meaningful.
If you just have one table, no big deal. When you have several all with ID in them, then access can get confused and more work for you.

Same with subforms. Access defauts the subform control name to the same as the subform. I prefer to rename the subfomr control so that I know which item I am referring to.

You will likely develop your own way, but you can learn by my mistakes, as I started off with ID in all of my tables for my first DB. Quickly learnt not to do that again.

[OlBill]

I started all over. I have attached my current table relationships. My Lead one is picking up the AssetIDFK to match the AssetIDPK fine. My Asbestos and Asbestos Visual are not, as can be seen in the snips. I have all the relationships set the same as the lead one.
One-to-Many. Enforce Referential Integrity check. Cascade Update Related Fields Check. Cascade Delete Related Records not check. Join Type-Only include rows where the joined fields from both tables are equal.

I'm so close! Somebody please!

[OlBill]

Good Lord that's big.

[Welshgasman]

Possible crosspost now at https://www.access-programmers.co.uk...issues.324813/