Design Question involving composite table

[csolomon]

Hello:

I was wondering if I could get some input on how to address a design
issue, involving my composite table.

I have one portion of my project complete. The following forms and
reports I will add, piggyback off of my existing design.
The part I have already completed allows my users to create a design
sample; this is made up of several materials to create one sample. I
have accomplished this using this design:

tblMixDesign:
DM_Mix (PK), DM_SampleNO, DM_Dt, etc

tblMaterial:
materialID (PK), matTypeID (FK), material, materialGrav

tblMixSample (composite table joining tblMixdesign and tblMaterial)
DM_Mix (PK1), DM_MaterialNo (PK2), matTypeID (FK), materialID (FK),
matBatchWeight

both Mixdesign and tblMaterial have a one to many relationship with
tblMixSample.

My question is related to another table I believe I will need to join
to the composite table, tblMixSample (an of course its parent table
tblMixDesign). The table, tblMCorrections, is related to the
composite table in my opinion. The materials selected by the user in
the tblMixSample, are the same materials that will have information
related to them in the tblMCorrections table.
My issue is that when I tried to connect the tables, access would not
allow me to connect the primary keys. Is it because there are two
primary keys in the composite table?

Here is the tblMCorrections:
correctionsID(PK), DM_Mix (FK), corr_Dt (Date), corr_wetGs,
corr_dryGs, corr_absorption

This table relates to the others (MixDesign and MixSample) because
when the material type (matTypeID) is Fine or Coarse (selected in
tblMixsample), these materials are measured for their moisture. the
user will enter how much they measure the material weight to be wet,
dry, and also the absorption (all user inputs, stored in the
tblMCorrections). As I stated i tried to link the MCorrections to
the MixSample, so as to carry over the material type (Fine and
Coarse), but was unable to do so because access would not allow me to
connect the keys. How can I illustrate the relationship if I can not
connect the keys?

[FishVal]

Hello, csolomon.

@csolomon
Now the relation looks fine - MixSampleCorrections.CorrectionID is FK(MCorrections).

As far as your other comments go, I have been trying to work with the design that was suggested and I have had NO luck relating the Mcorrections back to a sample number when I have the Material FK in the Mcorrections table. I will work again to try to get it working. I have reposted the ERD for you to view.

Ok. Tables structure looks quite good but the one thing, which is mostly my fault. :(
[MixSample] relates to [MixSampleCorrections] as 1toM which is not right.
Really, a single material being a part of a single mixsample could have one correction or no correction at all. This defines the relation as 1to1 making MixSampleCorrections.DM_MaterialNo primary key. This, BTW, means that MixSampleCorrections.correctionsID field could be moved to [MixSample] table.
However, this is not that critical and you could proceed with either design:

• [MixSample] table with correctionsID field.
• [MixSample] table related as 1to1 to [MixSampleCorrections] table.

But, this is really not so crucial, to say nothing about that the 2nd option is somewhat more preferrable from position of database normalization.

So, now queries.

• To obtain list of valid corrections:
  [qryValidCorrections]
  

  SELECT MCorrections.*, MCTests.*, ...<and all calculated fields>.... FROM MCorrections INNER JOIN MCTests ON MCorrections.correctionsID, MCTests.correctionsID WHERE MCTests.corrValid;
   

• To join the list above with relevant sample materials
  [qryMixSampleMaterialsCorrections]
  

  SELECT MixSampleCorrections.DM_MaterialNo, qryValidCorrections.* FROM MixSampleCorrections INNER JOIN qryValidCorrections ON MixSampleCorrections.correctionsID = qryValidCorrections.correctionsID;
   

• Now join MixSample records with correspondent corrections with outer(left) join because not all mixsample materials have corrections.
  

  SELECT MixSample.*, qryMixSampleMaterialsCorrections.* FROM MixSample LEFT JOIN qryMixSampleMaterialsCorrections ON MixSample.DM_MaterialNo = qryMixSampleMaterialsCorrections.DM_MaterialNo;
   

Regards,
Fish

[csolomon]

Fish,

"Really, a single material being a part of a single mixsample could have one correction or no correction at all. This defines the relation as 1to1 making MixSampleCorrections.DM_MaterialNo primary key."
>> A single material being a part of a single mixSample could have one correction or more corrections or none at all...In my opinion...for example if we have 10 ingredients, 4 will have tests ran on them at least one time (or many times).
Also, regarding inputting the information related to the MCorrections. I have a form and subform. the parent form, F_MCorrections has a control source to the table, and the child form, SF_MCTests has a control source to the MCTests table. From this design, how can my users select the materials that will be tested???

I have tried a query, but I get no useable results. I realize this is because there is nothing in the table...but I can not input anything related to the Moisture corrections with out being able to select a material that is being tested.

the MCorrections table looks like this:
correctionsID, materialID, corrDt

I tried to use cascading combo boxes, using this query for the first:
SELECT MixDesign.DM_SampleNo FROM MixDesign;
which gives me a list of all Sample Numbers available

And this query for the second combo box:
SELECT Material.materialID, Material.material, MixSample.DM_MaterialNo, MixDesign.DM_SampleNo FROM MixDesign INNER JOIN (Material INNER JOIN MixSample ON Material.materialID = MixSample.materialID) ON MixDesign.DM_Mix = MixSample.DM_Mix WHERE (((Material.materialID)=[MixSample].[materialID]) AND ((MixDesign.DM_SampleNo)=[Forms]![MCorrections]![cboSampNo]) AND ((Material.matTypeID)

this gives me a list of all applicable materials used in the mix sample selected in combo box1 that will need to have the test performed on them. The issues is this: although I can get the materials needed for the test, since box1 is unbound, the results to not stick to the fields in the table (combo2 is bound to the MCorrections.materialID field). I can not relate those back to the Sample Number on the form. Currently the list is all the materials needing to be tested as opposed to listing them by the sample number they are related to, which in my mind is the logical sequence of events.

"To join the list above with relevant sample materials
[qryMixSampleMaterialsCorrections]"
--This table is not populated...and I am not sure how to use it so that it will be populated. I tried to put it on the same form as the MCorrections and MCTests, but it does not work (INMO) because it's PK is a composite of DM_MaterialNo and CorrectionsID, and although this method allows the correctionsID to be input (it is connected parent to child on the form via correctionsID) the DM_MaterialNo, is not completed and I am not sure how to get it filled in.

[FishVal]

@csolomon
I mean correction assigned by user to a given material in a given mixsample chosen from several possible.

Also, regarding inputting the information related to the MCorrections. I have a form and subform. the parent form, F_MCorrections has a control source to the table, and the child form, SF_MCTests has a control source to the MCTests table. From this design, how can my users select the materials that will be tested???

I have tried a query, but I get no useable results. I realize this is because there is nothing in the table...but I can not input anything related to the Moisture corrections with out being able to select a material that is being tested.

the MCorrections table looks like this:
correctionsID, materialID, corrDt

I tried to use cascading combo boxes, using this query for the first:
SELECT MixDesign.DM_SampleNo FROM MixDesign;
which gives me a list of all Sample Numbers available

And this query for the second combo box:
SELECT Material.materialID, Material.material, MixSample.DM_MaterialNo, MixDesign.DM_SampleNo FROM MixDesign INNER JOIN (Material INNER JOIN MixSample ON Material.materialID = MixSample.materialID) ON MixDesign.DM_Mix = MixSample.DM_Mix WHERE (((Material.materialID)=[MixSample].[materialID]) AND ((MixDesign.DM_SampleNo)=[Forms]![MCorrections]![cboSampNo]) AND ((Material.matTypeID)

this gives me a list of all applicable materials used in the mix sample selected in combo box1 that will need to have the test performed on them. The issues is this: although I can get the materials needed for the test, since box1 is unbound, the results to not stick to the fields in the table (combo2 is bound to the MCorrections.materialID field). I can not relate those back to the Sample Number on the form. Currently the list is all the materials needing to be tested as opposed to listing them by the sample number they are related to, which in my mind is the logical sequence of events.

That is an interface issue and being so it could be addressed via many different methods. I have no idea as for what method is preferrable in your case just because I have no idea what interface you want to build to facilitate "logical sequence of events". :)

"To join the list above with relevant sample materials
[qryMixSampleMaterialsCorrections]"
--This table is not populated...and I am not sure how to use it so that it will be populated. I tried to put it on the same form as the MCorrections and MCTests, but it does not work (INMO) because it's PK is a composite of DM_MaterialNo and CorrectionsID, and although this method allows the correctionsID to be input (it is connected parent to child on the form via correctionsID) the DM_MaterialNo, is not completed and I am not sure how to get it filled in.

What table is not populated and what "didn't work when you put it on the same form"? :)
The query just illustrates how the data from your tables could be recalled to represent mixsample breakdown by used materials joined with relevant corrections.

Tables structure and interface are very separate things.
Tables structure must provide a consistent and reliable schema of data storage allowing to retrieve needed data by means of database engine. Since database engine is build with a definite rules inside, tables structure has to be designed in accordance with it.
On the other hand interface coding is an area where you could use your creative approach freely defining the ways for user to deal with data. Even using somewhat limited Access interface tools. :)

Now, if you MOL satisfied with current database structure - that means you see it logically consistent and in accordance with business rules of your application, you could design an interface of your dream. If you have any questions, then feel free to ask - I'll be glad to help you.

Regards,
Fish

[csolomon]

HI FISH! Happy New Year!

"That is an interface issue and being so it could be addressed via many different methods. I have no idea as for what method is preferrable in your case just because I have no idea what interface you want to build to facilitate "logical sequence of events". :)"

>>I thought that since I could not get the interface working properly, it was due to a bad design.

"What table is not populated and what "didn't work when you put it on the same form"? :)
The query just illustrates how the data from your tables could be recalled to represent mixsample breakdown by used materials joined with relevant corrections."
>>The composite table is not being populated (tblMixsampleCorrections). I tried to add it to my form as a 2nd subform, thinking that by having it linked to the key in the first form, the fields in the table (tblMixsampleCorrections, DM_MaterialNo, correctionsID) would automatically get updated when a new correction test was being performed, but that didn't happen, so I thought there was a design problem.

"Now, if you MOL satisfied with current database structure - that means you see it logically consistent and in accordance with business rules of your application, you could design an interface of your dream."
>>What do you mean by MOL?

I am going to attach my ERD again so that you can see what my design looks like (as a refresher :))

My issue with the user interface: It seems to me that during the process of allowing the user to choose what materials they will test, they:
1) Select the sample number they would like to have materials tested for
2) After the materials are populated based on the sample number, the user can input information related to the test (weight dry, weight wet, water absorption). THis information will serve as the current valid record. this record will be invalidated when a new test is performed.
3)If when checked after 24 hours, the test is not what it should be, the test will be completed again (invalidating previous record) until it reaches normal or acceptable levels.

I have set up my form to accomplish these tasks, but in order to join the MixSampleCorrections table with the others in the query, that table needs to get filled out some way, which I haven't figured out yet. I would like for you to see the form. I have been trying for at least an hour to get the file(s) small enough to upload here, but I have not been successful...Is there anyway I can email you the screenshot?
I appreciate any assistance you can provide.

[csolomon]

Hi Fish,

I have been working to sift through the problem of getting the MixSampleCorrections table populated. I have been unsuccessful and would like your input on how to do it. i thought i could do it if I was able to get DM_MaterialNo in the record source, but when i try, I get no records...I am at a loss at this point.

Chanell