Relationship Issues (1 Viewer)

[OlBill]

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.

Understood, it won't happen again.

 

[OlBill]

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?

I tried one table. The two types of tests have similar data, but there are some differences. The results are different. The Abater may be different for Lead than it was for Asbestos. The dates will be different for each. The Group and For may be different for each. The Sample IDs and Material color will always be different.

I could very well be wrong, but I don't see how one table is better given these differences.

The way it is set up now is working great on the tables and the form. I haven't figured out how to do the query. It is only giving me back the results of the AssetDFK that have them both, which right now is one out of three Assets.

 

[MajP]

I could very well be wrong

Yes most likely you are correct at being wrong. You do not make two tables that have the same fields to separate a category of data.

The Abater may be different for Lead than it was for Asbestos. The dates will be different for each. The Group and For may be different for each. The Sample IDs and Material color will always be different.

If the types of data was different such that you had some different kinds and number of fields then there may be a reason for different tables. What you describe above is different data going into the same fields between the two tables. That can be handle through code or a different input form. For example if the sample is asbestos then the pulldowns have a certain set of groups, or the color pull down has a certain set of colors. Then if the sample is lead the pulldown choices change. However those choices are going into identical fields.

 

[OlBill]

Dawn just broke over Marble Head. One table is working fine. And now my query works.

 

[OlBill]

What you describe above is different data going into the same fields between the two tables.

No, it is different data going into different fields. For example, there is a LeadFor and an AsbestosFor - they are different fields and may or may not be the same data. They are different fields because they are different types of tests.

 

[GPGeorge]

No, it is different data going into different fields. For example, there is a LeadFor and an AsbestosFor - they are different fields and may or may not be the same data. They are different fields because they are different types of tests.

Please give examples of "the data" here. We're on familiar territory for a lot of experienced Access developers, who see problems of the sort being pointed out frequently. If one of those data points is, for example, "parts per million" and the other is "pounds", then, yes, they are different types of data. That potentially calls for one approach to table design. On the other hand, if both are measured in the same way, then that calls for a different approach to table design.

YOU know what "LeadFor" means, and what "AsbestosFor" means. I'll be honest, I've never seen the term "Abater" used before, and certainly not in the current context. We do not know what they all mean. If we ask questions it is because, well, we've seen similar design questions multiple times and had many experiences helping newer Access users figure out how to handle them. We don't just leap to a conclusion until we are reasonably sure we have all the relevant facts.

So, please do give examples of "the data" here. Thank you.

 

[MajP]

IMO there is a no "correct" design when you have an "entity" (tests) but different fields under different conditions. It depends on how common: 80%, 90%, 95%... how you would handle the table/s design. I would always lean towards getting a single table design until I determine it is just too different to be feasible.

 

[OlBill]

Please give examples of "the data" here. We're on familiar territory for a lot of experienced Access developers, who see problems of the sort being pointed out frequently. If one of those data points is, for example, "parts per million" and the other is "pounds", then, yes, they are different types of data. That potentially calls for one approach to table design. On the other hand, if both are measured in the same way, then that calls for a different approach to table design.

YOU know what "LeadFor" means, and what "AsbestosFor" means. I'll be honest, I've never seen the term "Abater" used before, and certainly not in the current context. We do not know what they all mean. If we ask questions it is because, well, we've seen similar design questions multiple times and had many experiences helping newer Access users figure out how to handle them. We don't just leap to a conclusion until we are reasonably sure we have all the relevant facts.

So, please do give examples of "the data" here. Thank you.

LeadFor and AsbestosFor are people's names. They will not be the same for both - any one of 100 and constantly changing. Or might be. One Asset can have multiple tests of each type over several months for different people. Abater is the group doing the testing. A company name. They come and go.

I have input the data for 8 different assets into the one table version. I have built a query and it is working. I have built a report based on the query and it is pretty much working.

The only issue I am having now is that on my frmAsbestosSampleSub, after I save the DB and open it back up, it is skipping rows that I didn't skip when I put the data in. It looks like it is trying to align with the data from the frmLeadSampleSub or leave room for it. Of course the same thing is happening on the Report.

 

[OlBill]

IMO there is a no "correct" design when you have an "entity" (tests) but different fields under different conditions. It depends on how common: 80%, 90%, 95%... how you would handle the table/s design. I would always lean towards getting a single table design until I determine it is just too different to be feasible.

The tests are actually 3 different entities. The only thing they have in common all the time is the data from tblAsset - Unit, AssetID and AssetType.

I am using one table now.

 

[OlBill]

Examples of Data
tblAsset
Unit - 1001
AssetID
CU01P003
AssetType
Equipment

tblSample

LeadFor
Bill

LeadGroup
Maint

LeadSampleID
PB 091922BS-05

LeadSampleDate
9/19/2022

LeadAbater
Acme

LeadMatColor
Red

LWO
123456

LeadResult
0.365

LResultsDate
9/19/2022

AsbFor
Bob

AsbGroup
RandM

AsbSampleID
ASB 100922AI-01

AsbSampleDate
10/09/2022

AsbAbater
Abater Inc.

AsbMatColor
White

AsbWO
654321

AsbDetected
No/Yes

AsbResultsDate
10/12/2022

 

[OlBill]

This is what I currently have with one table and the data above.

 

[GPGeorge]

LeadFor and AsbestosFor are people's names. They will not be the same for both - any one of 100 and constantly changing. Or might be. One Asset can have multiple tests of each type over several months for different people. Abater is the group doing the testing. A company name. They come and go.

I have input the data for 8 different assets into the one table version. I have built a query and it is working. I have built a report based on the query and it is pretty much working.

The only issue I am having now is that on my frmAsbestosSampleSub, after I save the DB and open it back up, it is skipping rows that I didn't skip when I put the data in. It looks like it is trying to align with the data from the frmLeadSampleSub or leave room for it. Of course the same thing is happening on the Report.

"LeadFor and AsbestosFor are people's names. "
That is, in fact, one type of data, not two. The VALUES are different, but they are one thing: People.

"Abater is the group doing the testing. A company name. They come and go."

Okay, so that was not clear from the context. You have a TestingGroup for each test? Again, if that's the case, they are ONE type of data, not two or more. The VALUES can be different, but they are one thing: Testing Companies.

It'll take a minute to evaluate the sample data, thanks.

 

[OlBill]

"LeadFor and AsbestosFor are people's names. "
That is, in fact, one type of data, not two. The VALUES are different, but they are one thing: People.

"Abater is the group doing the testing. A company name. They come and go."

Okay, so that was not clear from the context. You have a TestingGroup for each test? Again, if that's the case, they are ONE type of data, not two or more. The VALUES can be different, but they are one thing: Testing Companies.

It'll take a minute to evaluate the sample data, thanks.

Thank you GP. Yes, they are both names. However the names are or can be different depending on the type of test. So each type of test can have a different value.

 

[GPGeorge]

Thank you GP. Yes, they are both names. However the names are or can be different depending on the type of test. So each type of test can have a different value.

Yes, I'm afraid I am no longer following your line of thought. Perhaps if someone opens the sample accdb you uploaded they can suss out what you are talking about.

 

[GPGeorge]

After reviewing the tables in the sample uploaded, I confirmed that the table design did indeed reflect what is often referred to as a "spreadsheet style" table because it looks exactly like a spreadsheet imported into Access as a table. In fact, that's a very common way they get created.

The hallmark of such tables is the existence of multiple fields of the same type, named differently to reflect the data: LeadFor and AsbestosFor, for example. Both refer to the same thing, the person for whom a sample was taken. But sometimes the sample is for "Lead" and sometimes for "Asbestos". Unfortunately, those data points get encoded into the field names like these did.

Here's how I would recommend the tables be redesigned to reflect an appropriate Relational database design.

NOTE: Because results for Lead sampling and for Asbestos Sampling are different, it is actually correct to have two tables for those results, but ONLY for the results.
NOT: Fields which are common to both are in the Sample table, renamed SampleCorrected in this diagram, such as SampleFor, which replaces the two original "XXXXFor" fields. It includes one additional field, called SampleTypeID. There are two (and possibly more) values for SampleType, "Lead" or "Asbestos".

Finally, the two results tables both link to the sample table. Each set of results, however, is store in one or the other, depending on the sample Type.

I uploaded the accdb with these corrections. Further analysis is always possible, of course. The point is that we are now in the world of relational database applications, not the world of spreadsheets and the way tables are designed must reflect that environment to be practical.

 

[OlBill]

Thank you GPGeorge. I will try it out. I really appreciate it.

I have a question. On the frmInput on the one you corrected, in the Lead tab, it is skipping rows. There is data in PK055 and PK057, but not PK056. On the Asbestos tab, there are no skipped spaces. Is there a way to correct that?

 

[GPGeorge]

Thank you GPGeorge. I will try it out. I really appreciate it.

I have a question. On the frmInput on the one you corrected, in the Lead tab, it is skipping rows. There is data in PK055 and PK057, but not PK056. On the Asbestos tab, there are no skipped spaces. Is there a way to correct that?

I did not look at the forms because they are bound to the previous table. I strongly suspect, however, that the "missing" data reflects the fact that the original table design is a "spreadsheet" style with many fields in some records not populated. We can address forms after you work on the tables.

 

[OlBill]

I started over using yours for a model and added in some of the other suggestions from here. I decided a few of the other bits could be included in the normalization. If I am off track or missed anything, please feel free to correct me.
I really appreciate your help.

 

[OlBill]

I added one more: AssetType

 

[mike60smart]

Hi Bill

The attached is one way of doing this.