Relationship Issues (1 Viewer)

[OlBill]

I need some help. I have my tables built.
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 doing something incorrectly, I just don't know what it is. I'm really close, and would appreciate some advice.

 

[theDBguy]

Check your Lead form to see if you assigned it a Default Value.

 

[OlBill]

Check your Lead form to see if you assigned it a Default Value.

A default value? Where do I look? I've checked everything I can find on all the tables and they are the same.

 

[Gasman]

Possible crosspost first at https://www.accessforums.net/showthread.php?t=86605 ?

 

[theDBguy]

A default value? Where do I look? I've checked everything I can find on all the tables and they are the same.

Check the forms too.

 

[OlBill]

Check the forms too.

Thanks DBguy. I have. I have checked everything line by line.

 

[theDBguy]

Thanks DBguy. I have. I have checked everything line by line.

Not lines, did you check the properties? You can post a screenshot of the Data tab.

 

[OlBill]

Not lines, did you check the properties? You can post a screenshot of the Data tab.

Lead works. Asb doesn't

 

[plog]

1. Do you actually have data in tblAsbestosSample for AssetIDFK=1?

2. Do you have the parent/child relationship using the correct field names? Or did you just copy the Lead subform over and not update the child name?

 

[OlBill]

Here is the PK the lead is pulling from and the ASB won't.

 

[OlBill]

1. Do you actually have data in tblAsbestosSample for AssetIDFK=1?

2. Do you have the parent/child relationship using the correct field names? Or did you just copy the Lead subform over and not update the child name?

1. I tried to put it in and this time it took it, but it also copied the same information to the Lead Tab. They need to be distinct.
2. No, I didn't copy it. I built it from scratch.

 

[OlBill]

This is what I have. I used a Tab Control on the Main Form frmInput and dragged the subs on to each tab.

 

[OlBill]

1. Do you actually have data in tblAsbestosSample for AssetIDFK=1?

2. Do you have the parent/child relationship using the correct field names? Or did you just copy the Lead subform over and not update the child name?

Thanks plog. The parent/child relationship was wrong on the two that weren't working. It is working like a champ now. You guys are geniuses!

 

[plog]

You didn't really answer my questions, I'm doubling down--you either don't have data in tblAsbestosSample for AssetIDFK=1 or you're form's parent/child relationship is wrong. However...

Looking more closely at your tables, you have set them up incorrectly. You do not need both tblLeadSample and tblAsbestosSample because they have the exact same structure. When you do that you are essentially storing data (Sample Type) that should be in a field in the table's names. Instead of 2 tables you need just 1 named tblSamples that has the same structure as those tables, but with an additonal field (SampleType) where you will put "Lead" or "Asbestos".

 

[theDBguy]

Thanks plog. The parent/child relationship was wrong on the two that weren't working. It is working like a champ now. You guys are geniuses!

Glad to hear you got it sorted out. Good luck with your project.

 

[OlBill]

Glad to hear you got it sorted out. Good luck with your project.

Thank you very, very much for your help.

 

[Pat Hartman]

Does that mean you are going to take the excellent advise to use a SINGLE table with a data field for "Lead" or "Asbestos"? or are you going to leave the poor structure? What about "Formaldehyde"? and potentially other dangerous contaminants?

Taking the small view prevents you from easily being able to track other issues should the need arise. Who knew about the "Formaldehyde" problem until China sent us all that bad drywall. I think it only ended up in some locations in the US but who knows when the next problem will happen. Wouldn't you like to be able to just add a row to a "type" table and have all your forms and reports just work?

Properly normalizing tables keeps you from making totally unnecessary limits that prevent easy expansion.

 

[Gasman]

You didn't really answer my questions, I'm doubling down--you either don't have data in tblAsbestosSample for AssetIDFK=1 or you're form's parent/child relationship is wrong. However...

Looking more closely at your tables, you have set them up incorrectly. You do not need both tblLeadSample and tblAsbestosSample because they have the exact same structure. When you do that you are essentially storing data (Sample Type) that should be in a field in the table's names. Instead of 2 tables you need just 1 named tblSamples that has the same structure as those tables, but with an additonal field (SampleType) where you will put "Lead" or "Asbestos".

Post #7 out of 45 in the crosspost said exactly that back on the 7th September?

Linking Tables for Queries, Forms and Reports

Hello, it's been a while since I've been here. All the help I received before kept me from having to come back. I have two tables Lead tbl and Asbestos tbl I have a form to input the information. Right now I am set up like this. I have tried it every way I can think of. 48656...

www.accessforums.net

 

[Pat Hartman]

Post #7 out of 45 in the crosspost said exactly that back on the 7th September?

And that @OlBill is why experts are annoyed when people cross post. If you can't wait 10 minutes for an answer, at least post a link to the other post so we don't waste our time giving you the same advice you are already ignoring in another post.

 

[OlBill]

You didn't really answer my questions, I'm doubling down--you either don't have data in tblAsbestosSample for AssetIDFK=1 or you're form's parent/child relationship is wrong. However...

Looking more closely at your tables, you have set them up incorrectly. You do not need both tblLeadSample and tblAsbestosSample because they have the exact same structure. When you do that you are essentially storing data (Sample Type) that should be in a field in the table's names. Instead of 2 tables you need just 1 named tblSamples that has the same structure as those tables, but with an additonal field (SampleType) where you will put "Lead" or "Asbestos".

AssetIDFK=1 does not have an asbestos sample, it only has Lead right now. Not every asset has all three. tblLeadSample and tblAsbestosSample have very similar structure, but they are not exactly the same. The results are reported differently. And some assets will have one, some will have two, some will have all three.