By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Design Question involving composite table

100+
P: 166
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?
Dec 15 '08 #1
Share this Question
Share on Google+
54 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, solomon.

@csolomon
First of all about the existing tables:
  • tblMixSample.matTypeID is redundant field as long as tblMaterial.matTypeID contains the same information.
  • Why do you use composite PK in tblMixSample?

The rest makes little sense for me so far?
Could you elaborate on this.


Regards,
Fish
Dec 15 '08 #2

100+
P: 166
Hi Fish,

I appreciate your response.

"First of all about the existing tables:
tblMixSample.matTypeID is redundant field as long as tblMaterial.matTypeID contains the same information.
>>MatTypeID is in the mixType table because a user can select many matTypes for one or more MixDesigns...the selection has to be made by the user.
Why do you use composite PK in tblMixSample?
>>a composite key is used because i needed to identify which record matched the record in MixDesign (DM_MIx) as well as just to have the individual records be identified in the MixSample (via the PK2). I wont ever use the PK2 to identify the records, but it was suggested to me to use the composite key.

What would you like to me to elaborate on? As I stated, I need to create a relationship that depicts these business rules (I already have the first two):

1)Each DesignMix will have many materials./Many materials will be assigned to one or more DesignMixes
2)Each DesignMix will have many material types./ Many material types will be assigned to one or more DesignMixes
3)Each Mix Sample (tblMixSample) will have one or more Moisture Corrections (tblMCorrections)./Each Moisture Correction will be assigned to one or more Mix Samples

here is an illustration of what my relationships (1and 2) look like

DesignMix
DM_Mix/DM_sampleNo/DM_Dt/jobNumber
1/ 1984/ 12.15.2008
2/ 1985/ 12.01.2008

MatType
MatTypeID, MatType
1/Cement
2/Fine Aggregate
3/ Coarse Aggregate

Material
materialID/ matTypeID/ material
1/ 1 (Cement) / gravel1
2/ 1 (Cement)/ gravel2
3/ 2 (Fine)/ sand1
4/ 2 (Fine)/ sand2
5/ 3 (Coarse)/ coarse1

MixSample-composite of MixDesign and material
DM_MIx (PK1)/ DM_MaterialNo (PK2)/ matTypeID, materialID, matBatchWeight
1/ 1/ 1(Cement)/ 1 (gravel1)/ 15
1/ 2/ 1(Cement)/ 2 (gravel2)/ 2.03
1/ 25/ 2 (Fine)/ 3(sand1)/ 25
2/ 33/ 2 (Fine)/ 3 (gravel1)/ 44
2/ 32/ 2 (Fine)/ 4 (gravel2)/ 45
2/ 22/ 3 (Coarse)/ 5 (coarse1)/ 44
As you can see, many matTypes (cement, fine, coarse, etc) and many materials (gravel1, sand1, gravel2, sand2, coarse1) can be assigned to one or more DesignMixes

MCorrection is related to the DesignMix because there will be information recorded in the MCorrections table that relates to the certain material types selected, specifically Fine and Coarse matTypes.

My issue is how do I relate them in the tables? Do i need another composite table connecting MixSample (Mix Sample actually holds the material and material types related to the DM_Mix) and MCorrections?
Dec 16 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Hello, solomon.

@csolomon
Does that mean mixType.MatTypeID is not the same as tblMaterial.MatTypeID for a given materialID value?

@csolomon
Why [DM_Mix] together with [DM_MaterialNO] are composite PK?
From your explanation [DM_MaterialNO] has a unique value.
So you either use [DM_MaterialNO] as PK, or [DM_Mix] and [materialID] as composite PK.

@csolomon
1) A classic many-to-many relationship. You almost have this, just assign PK/FK fields in a proper way.
2) So, again, what material type is related to - material itself or material in a given design mix?
3) That makes a very little sense. For what moisture tests are being performed? Do they measure material moisture level or mixsample moisture level?

Kind regards,
Fish.
Dec 16 '08 #4

100+
P: 166
"Does that mean mixType.MatTypeID is not the same as blMaterial.MatTypeID for a given materialID value?"
>>MixType.MatTypeID was a typo...I meant to say that MixTypeID is in the MixSample table because a user can have many mixTypesID and many MaterialIDs related to one sample.
The MatTypeID in the tblMaterial just illustrates that there can be many materials of the same MatType

Let me clearly state what a MixSample is: a combination of many material types and material make up one mixSample.

"Why [DM_Mix] together with [DM_MaterialNO] are composite PK?
From your explanation [DM_MaterialNO] has a unique value.
So you either use [DM_MaterialNO] as PK, or [DM_Mix] and [materialID] as composite PK."
>>The MixSample.DM_Mix represents the DM_Mix (let's refer to it as Sample Number, as that is what is). DM_MaterialNo (PK) represents each individual record in the table, regardless of the sample number (DM_Mix) it belongs to. The joint primary key is what is used to identify the specific sample number the record belongs to. MaterialID could be used as a part of the composite key...
"1) A classic many-to-many relationship. You almost have this, just assign PK/FK fields in a proper way.
2) So, again, what material type is related to - material itself or material in a given design mix?
>>MixSample.matTypeID is related to the MixDesign because each material type can be used more than once when making a sample. The user can then select the MixSample.materialID, based on the matTypeID that is selected.
3) That makes a very little sense. For what moisture tests are being performed? Do they measure material moisture level or mixsample moisture level?"
>>Moisture tests are measured on the coarse and fine materials in the mix sample.
Dec 16 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Ok.

Post please your tables metadata clearly outlining relationships and field datatypes.
Like in the sample below:

Table: [tblMaster]
keyMasterID, Long(Autonumber), PK
txtMaster, Text

Table: [tblChild]
keyChildID, Long(Autonumber), PK
keyMasterID, Long, FK(tblMaster)
txtChild
Dec 16 '08 #6

100+
P: 166
table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
jobNumber, Number, FK
panID, Number, FK
toneID, Number, FK
finishDepID, Number, FK
FinishActID, Number, FK
DM_pan, Number
DM_SackMix, Number
DM_waterRatio, Number
DM_entAir, Number
DM_Memo, Memo
DM_DtNeeded, Dt
DM_Requestor, text

Table: MixSample
DM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2
matTypeID, Number, FK
materialID, FK
matBatchWeight, Number
pigPercent, Number

Table: MatType
matTypeID, Number PK
matType, Text

Table: Material
materialID, Number, PK
matTypeID, Number, FK
material, Text
materialGrav, Number

Table: MCorrections
correctionsID, AutoNumber, PK
DM_Mix, DM_materialNo, Number, composite FK
CorrDt, Date
corrDryGs, Number
corrWetGs, Number
corrAbsorption, Number

I believe that this design may work, but I am not sure. Each of the materials in the MixSample table (per SampleNo, or DM_Mix) will have the a record related to it in MCorrections. On the form, since I can not have a subform on a continuous form, it's not really working out like i think it should, which makes me question my design. I'd like for my form to show all of the records, per SampleNo, and then I'd like to have the user input the MCorrections. I have tried using datasheet, but my form always changes back to the single for format.

Thank You
Dec 17 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Nice.

However it would be better to know exactly to which table FK belongs.
See the example I posted previously.
Dec 17 '08 #8

100+
P: 166
table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
jobNumber, Number, FK (Job Table, not listed)
panID, Number, FK (PanSize Table, not listed)
toneID, Number, FK (Tone Table, not listed)
finishDepID, Number, FK (FinishDep Table, not listed)
FinishActID, Number, FK (FinishAct Table, not listed)
DM_pan, Number
DM_SackMix, Number
DM_waterRatio, Number
DM_entAir, Number
DM_Memo, Memo
DM_DtNeeded, Dt
DM_Requestor, text

Table: MixSample
DM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2
matTypeID, Number, FK (MatType Table)
materialID, FK (Material Table)
matBatchWeight, Number
pigPercent, Number

Table: MatType
matTypeID, Number PK
matType, Text

Table: Material
materialID, Number, PK
matTypeID, Number, FK (MatType table)
material, Text
materialGrav, Number

Table: MCorrections
correctionsID, AutoNumber, PK
DM_Mix, DM_materialNo, Number, composite FK (MixSample table)
CorrDt, Date
corrDryGs, Number
corrWetGs, Number
corrAbsorption, Number
Dec 17 '08 #9

FishVal
Expert 2.5K+
P: 2,653
Ok. Very well.
I'll look into it and will return to you in a couple of hours.
Dec 17 '08 #10

100+
P: 166
OK

Thank you Kindly!
Dec 17 '08 #11

FishVal
Expert 2.5K+
P: 2,653
Ok.
  • Relation between [MixSample] and [Material].
    Considering sample data provided by you in post #3 I could say that MixSample.matTypeID field is definitely redundant. Really [MixSample] does not contain records with matTypeID/materialID combinations others than those in [Material] table. That means - [Material] table FK in [MixSample] table unambiguously determines materialID and matTypeID. The same you've already stated in words - [MatType] is in 1-to-many relationship with [Material]. However your current design presume many-to-many relationship between [MatType] and [Material].
  • Relation between [MixSample] and [Material].
    There is no reason for MixSample.DM_Mix being FK(MixDesign) to be also a part of composite primary key. [DM_MaterialNo] is an exellent candidate to PK.
  • [MCorrections] table.
    As long as moisture tests are being performed for materials, results of these test should be stored in a table related to [Material] as many-to-one. These results should be then used to calculate corrections whatever you need for mixed sample.
It may be not clear so far for you how these relations could be used, well I will be glad to help you design appropriate queries, but that is how the data storage should be organized if I'm not missing something. Does it make sense?

Regards,
Fish
Dec 17 '08 #12

100+
P: 166
Hi Fish,

I disagree with your first point: "I could say that MixSample.matTypeID field is definitely redundant. Really [MixSample] does not contain records with matTypeID/materialID combinations others than those in [Material] table. That means - [Material] table FK in [MixSample] table unambiguously determines materialID and matTypeID. "
>>I don't know if I agree that it is redundant because on my form I have a cascading combination box which first allows the user to select the MatType and then the second combo box is populated with the materials related to that mix type...the user then selects all of the matTypes and materials they will be using for the sample.
"However your current design presume many-to-many relationship between [MatType] and [Material]. "
>>Currently I have a one to many relationship between MatType and Material. Meaning there will be many materials of one Material Type

Your second point: Relation between [MixSample] and [Material].
There is no reason for MixSample.DM_Mix being FK(MixDesign) to be also a part of composite primary key. [DM_MaterialNo] is an exellent candidate to PK.
>>I agree that I may be able to accomplish my goal in this table with out a composite key and just have the MixSample.DM_Mix as a FK...since I already have that relationship set up as a composite key, what damage would it do if it stayed that way? It works to identify individual records in the table as well as tie that record to a specific MixDesign record.

Your third point: [MCorrections] table.
As long as moisture tests are being performed for materials, results of these test should be stored in a table related to [Material] as many-to-one. These results should be then used to calculate corrections whatever you need for mixed sample.
>>Moisture tests are performed for the matTypes that have been selected by the user for the mix samples, in the MixSample table, which is why I felt it needed to be connected to that table as opposed to the Material table because the material table is just a list of all available material to the user.
Dec 17 '08 #13

FishVal
Expert 2.5K+
P: 2,653
@csolomon
:D Beleive me or not, but your table design presume many-to-many relationship between MatType and Material regardless of what you are considering about it.

@csolomon
Composite primary keys are always troublesome. The one but not the only reason is that form control chould be bound to a single table field only, so having composite PK and, thereby, composite FK you will need to hold pair of controls and code a synchronizing logic.
Being on your place I would avoid this situation. But, obviously, I'm not on your place and your are certainly allowed to do what you consider to be suitable. ;)

@csolomon
Does it mean that record in [Material] table is basically an abstract material name? Is there a table that contains a list of real material batches held in stock and used to prepare samples?
Dec 18 '08 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
OK the simple answer to the problems you are having with the MixSample table are the fact that you are missing a join table. Fish is right when he says that you have a many to many relationship. What he means is this:

If the tables were as follows:

table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
etc.

Table: MixSample
DM_MaterialNo, AutoNumber, PK
matTypeID, Number, FK (MatType Table)
materialID, FK (Material Table)
matBatchWeight, Number
pigPercent, Number

Which is how they should be. Then you have a many to many relationship between these two tables. You cannot solve this by adding a composite primary key to the MixSample Table including the primary key of MixDesign. It just doesn't work.

What you need to do is create a join table to handle the many to many relationship as follows:

Table: DesignSample
DM_Mix, Number, PK
DM_MaterialNo, Number, PK

Now the only fields that you can include in this table are those that depend on BOTH of these keys. Those fields that only depend on DM_MaterialNo stay in the MixSample table.

Now I am not sure if the corrections table relates to MixSample alone or to both MixSample and MixDesign. If it is MixSample alone then the FK is simply the PK of MixSample. If it depends on both then briefly explain the relationship and I will help you incorporate it.

You should also check out this article on http://bytes.com/topic/access/insigh.../2#post2296372 to help you to understand the rules on creating tables and their relationships.
Dec 18 '08 #15

100+
P: 166
Fish and Msquared:

MixSample is my join table. It represents the business rule:
Each MixSample will have many materials.
Many materials will be used to make each mix sample
I created this join table because before, there was a many to many relationship between the DesignMix and Material Tables. This relationship is working fine.


table:MixDesign
DM_Mix, AutoNumber, PK
DM_SampleNo, Number
DM_Date, Date
jobNumber, Number, FK (Job Table, not listed)
panID, Number, FK (PanSize Table, not listed)
toneID, Number, FK (Tone Table, not listed)
finishDepID, Number, FK (FinishDep Table, not listed)
FinishActID, Number, FK (FinishAct Table, not listed)
DM_pan, Number
DM_SackMix, Number
DM_waterRatio, Number
DM_entAir, Number
DM_Memo, Memo
DM_DtNeeded, Dt
DM_Requestor, text

Table: Material
materialID, Number, PK
matTypeID, Number, FK (MatType table)
material, Text
materialGrav, Number

Join Table: MixSampleDM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2

materialID, FK (Material Table)
matTypeID, Number, FK (MatType Table)
matBatchWeight, Number
pigPercent, Number

Fish, please explain to me why you feel this way:
":D Beleive me or not, but your table design presume many-to-many relationship between MatType and Material regardless of what you are considering about it."
Here are the two tables:
Table: MatType
matTypeID, Number PK
matType, Text

Table: Material
materialID, Number, PK
matTypeID, Number, FK (MatType table)(<--This creates the one matType to many materials realtionship)
material, Text
materialGrav, Number

If you are referring to the fact that the MixSample table has matType as well as materialID there, it is only so the user can select the matType, prior to selecting the material. I suppose I don't need it since when a user selects the material, the matType could be found by accessing the Material table via a DLOOKUP. The reason I wanted the user to select the matType first is because I am emulating an excel sheet and it is broken down by the type of materials used, then the prospective materials used, of that type. So i was trying to make the process as close to the original as possible. I thought about just having a label that stated the material type and then just having my combo box have a query and setting the query to list the matType of the label, but then i'd have to have more combo boxes. The way I am doing it allows me to only have two, and the second combo box is based on the selection of the first.

My question really related to the MixSample table and the MCorrections table. I wanted to be able to represent the fact the business rule:
Each Material selected by the user (of MatTypes 2 and 3) will have one or more moisture corrections.
Each moisture correction will have one or more materials measured

I would like for you to see my ERD, so that you can see my relationships, and then judge my design.
Dec 18 '08 #16

100+
P: 166
Fish,

"Does it mean that record in [Material] table is basically an abstract material name? Is there a table that contains a list of real material batches held in stock and used to prepare samples?"

I am not sure what you mean by 'and abstract material name'. This is the table that contains the list of real materials in stock and used to prepare the samples. The table basically looks like this:

materialID/ matTypeID/material/materialGrav
7 1 Federal White 3.15
8 1 LeHigh Grey Type III 3.15
9 2 Amberlite B 2.65
10 2 Arrowood #78 2.92
11 2 Arrowood Black Rock 2.92
12 2 Black Obsidian
14 2 "Buff Limestone 1/2""2.56
37 2 Madras #7 2.67
38 2 "Mahogany Marble 9/16"" x 3/16"""
57 3 Arrowood Sand 2.92
61 3 Clark Sand 2.60
62 3 Colonial Red Sand 2.62
63 3 Coral Spar Sand 2.56
64 3 Dolcito Sand 2.77
65 3 FL Screenings 2.56
81 3 Ingram Con. Sand 2.59
82 3 UC Black #8315 2.82
2 4 DSC #2703 Black 4.04
90 4 Wheat 2.97
91 4 Grand Canyon 3.53
92 4 Hershey Chocolate 3.88
93 4 Deep Grey 3.20
94 4 Cave Grey 2.98
95 4 Charcoal 4.60
5 5 Rheomac VMA 362 1.02
6 5 Pozzolith 80 1.20

So in essence, when a mix sample is created, the user selects all the materials used to make the mix sample, which can be many.

For example, mix sample 1984 inclued these materials:
8 1 LeHigh Grey Type III 3.15
9 2 Amberlite B 2.65
10 2 Arrowood #78 2.92
11 2 Arrowood Black Rock 2.92
82 3 UC Black #8315 2.82
2 4 DSC #2703 Black 4.04
90 4 Wheat 2.97
91 4 Grand Canyon 3.53
5 5 Rheomac VMA 362 1.02
6 5 Pozzolith 80 1.20

All those materials are used to make ONE sample, 1984.
Dec 18 '08 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
OK I think I have a better understanding now.

Table: MixSample
DM_Mix, Number, PK1
DM_MaterialNo, AutoNumber, PK2
matTypeID, Number, FK (MatType Table)
materialID, FK (Material Table)
matBatchWeight, Number
pigPercent, Number

There are a few problems with this. As it is a Join table between MixDesign and Material tables then your composite primary key has to be made up from the primary keys of those two tables. DM_MaterialNo is no the primary key of the Material table, materialID is. You also reference matTypeID which is already accessed through the material table and this kind of duplication causes integrity problems. Your table should be as follows:

Table: MixSample
DM_Mix, Number, PK1 (referencing MixDesign table)
materialID, PK2 (referencing Material Table)
DM_MaterialNo, AutoNumber, PK2
matBatchWeight, Number
pigPercent, Number

If you need matTypeID in this table then you need to remove it from the material table.
Dec 18 '08 #18

FishVal
Expert 2.5K+
P: 2,653
@csolomon
Sure it is working fine. However, it will work fine if MixSample.DM_Mix FK(MixDesign) field is not a part of composite PK as well. So why pay more? Composite PKs are usually avoided even for less reasons. :)

@csolomon
Yes I'm referring to it.
Well, lets elaborate on this. I guess the form where your cascading combos are placed is viewed in "Single form" view. Am I right? Otherwise, I could hardly imagine that filtering of combo bound to [materialID] does not affect the rest rows of form being in Continuous or Datasheet view.
If the form is in "Single form" view you could easily achieve the same using unbound combo for [matTypeID]. If not, then there are many other tricks to build interface without incorporating into database structure rules that are against business rules of application.

My question really related to the MixSample table and the MCorrections table. I wanted to be able to represent the fact the business rule:
Each Material selected by the user (of MatTypes 2 and 3) will have one or more moisture corrections.
Each moisture correction will have one or more materials measured

I would like for you to see my ERD, so that you can see my relationships, and then judge my design.
If I've understood your correctly, moisture tests are being made for materials. On the basis of results of the rests corrections are being calculated for mixed samples. Am I right?
If so why not to store moisture tests results in relation to materials and use them to calculate corrections for mixed samples made from those materials?

Regards,
Fish.
Dec 18 '08 #19

100+
P: 166
The way you have the new table set up, you have 3 keys that make up a composite key...Fish was saying he didn't recommend the composite key (using two keys) at all, and now you are suggesting that I use 3...I am confused at this point as to why I need them at all if I have the FK (DM_Mix and materialID) which will represent the fact that each record is unique

"DM_MaterialNo is not the primary key of the Material table, materialID is. You also reference matTypeID which is already accessed through the material table and this kind of duplication causes integrity problems."
>>DM_material is an autonumber that makes the records in the SampleMix table unique...tied to the DM_Mix (PK of DesignMix) it makes the record unique to a record in the DesignMix table. As I understand it, I could just use the DM_materialNo as the PK and then have the DM_Mix as an FK, and it would still depict the same relationship.
If I take matTypeID out of the material table then my one to many relationship with the material table will be broken. The material type relates to the materials. So i have to have that relationship.
You stated that having matTypeID in the MixSample table would not enforce referrential integrity; I do not have the matTypeID connected to the MatType table, so there isnt' a relationship set up between the two, so integrity doesn't come in to play. If I take it out, the materialID would be getting recorded and not they matType, which I guess would be ok, since the material in the material table would tell which matType was used...So I can agree that I could take out the matTypeID, since as I stated, it isn't even connected to the MatType or Material tables...it's just keeps a record of what matType was used in the selection.
Dec 18 '08 #20

100+
P: 166
Hi Fish,

"Sure it is working fine. However, it will work fine if MixSample.DM_Mix FK(MixDesign) field is not a part of composite PK as well. So why pay more? Composite PKs are usually avoided even for less reasons. :)"
>>I can agree about the composite keys...it does create more work for me, so I will change it to have ONE PK
"Yes I'm referring to it.
Well, lets elaborate on this. I guess the form where your cascading combos are placed is viewed in "Single form" view. Am I right? Otherwise, I could hardly imagine that filtering of combo bound to [materialID] does not affect the rest rows of form being in Continuous or Datasheet view.
If the form is in "Single form" view you could easily achieve the same using unbound combo for [matTypeID]. If not, then there are many other tricks to build interface without incorporating into database structure rules that are against business rules of application."
>>The cascading combos are on a continuous subform so that the user can see all of his choices for the sample at once (the main form is a single form). I have decided that I could achieve the same thing is I take out the matTypeID from the MixSample table. I looked at my form and my cascading combo queries are straight from the Material and MatType tables, I could just make them unbound and I believe it would work the same way.

"If I've understood your correctly, moisture tests are being made for materials. On the basis of results of the rests corrections are being calculated for mixed samples. Am I right? If so why not to store moisture tests results in relation to materials and use them to calculate corrections for mixed samples made from those materials?"
>>If i understand you correctly (:D) You are asking if the moisture tests are being done on materials...The answer is yes, but not all the materials, just the ones selected by the user (stored in the MixSample table), which is why I thought the MixSample and MCorrections tables should have a relationship (1 to M) because the tests are performed on the materials in that table, which are an materials from the material table.
Dec 18 '08 #21

FishVal
Expert 2.5K+
P: 2,653
@csolomon
Well. Changing form controls (in your case materialID.RowSource when running combos cascade code) is always problematic, but where are workarounds. Like this, for example. I just wonder, didn't you encounter problems with empty comboboxes in previous records after filtering to [keyMatTypeID]?

@csolomon
:) Ok, lets deal with your main question.
Moisture tests results should be definitely related to [Material] table.
I guess, multiple moisture tests could be performed on a single material. So the table should contain test date and, optionally, date when test result becomes invalid or just the date of the next test is considered to be such.
[MixSample] table could be joined with this table on the same [materialID] and date comparing.
Is that your case, or you have NMT one test for one material?

Regards,
Fish
Dec 18 '08 #22

100+
P: 166
Fish,

"Well. Changing form controls (in your case materialID.RowSource when running combos cascade code) is always problematic, but where are workarounds. Like this, for example. I just wonder, didn't you encounter problems with empty comboboxes in previous records after filtering to [keyMatTypeID]?"
>>I do not plan to change the form controls, materialID.RowSource. I think it will still work from the tables.
I have not encountered problems with empty combo boxes because my combo boxes are never empty. Here is the code for my cascading combos:
Combo 1 for matTypeID:
SELECT MatType.matTypeID, MatType.matType
FROM MatType
ORDER BY MatType.matType;
Here is the VBA in the afterupdate event of combo 1

Private Sub cbo_matTypeID_AfterUpdate()
'Me.cbo_materialID = Null
Me.cbo_materialID.Requery
Me.cbo_materialID = Me.cbo_materialID.ItemData(0)

End Sub

Combo 2 for materialID:
SELECT Material.materialID, Material.material, Material.matTypeID, MatPrices.matPriceActive FROM Material INNER JOIN MatPrices ON Material.materialID = MatPrices.materialID WHERE (((Material.matTypeID)=[Forms]![F_MixDesign]![SF_MixSample].[Form]![cbo_matTypeID]) AND ((MatPrices.matPriceActive)=True)) ORDER BY Material.material;

Here is the VBA Code I have in the on current event of the subform:
Private Sub Form_Current()
' Requery the second combo
Me.cbo_materialID.Requery
End Sub

Private Sub material_GotFocus()
' Put the focus on the combo box behind
Me.cbo_materialID.SetFocus
End Sub

":) Ok, lets deal with your main question.
Moisture tests results should be definitely related to [Material] table.
I guess, multiple moisture tests could be performed on a single material. So the table should contain test date and, optionally, date when test result becomes invalid or just the date of the next test is considered to be such.
[MixSample] table could be joined with this table on the same [materialID] and date comparing.
Is that your case, or you have NMT one test for one material?"
>>Here is my MCorrections table:
correctionsID PK Auto
DM_Mix Number, DM_MaterialNo (currently composite FK)
corrDt Date- Date moisture of material was measured
corrDryGs Number - Material Dry Grams material measured
corrWetGs Number- Material Wet Grams material measured
corrAbsorption Number- Material water absorption
these are measured daily until the material measures what it is supposed to

Multiple tests are performed on the materials selected as apart of the mix by the user (stored in the SampleMix tbl)
I also found out that moisture corrections from previous measurements are invalid once new tests have been done...so today's results are invalid tomorrow.
Dec 18 '08 #23

FishVal
Expert 2.5K+
P: 2,653
@csolomon
So, what result is relevant for a given material in a given mixed sample?
That obtained in the same day the sample was prepared or the last available?

Regards,
Fish.
Dec 18 '08 #24

100+
P: 166
The results that are relevant are the results taken on the current day. If the measurements of the day are what they should be, there will be no more tests, otherwise, the tests will continue until they are what they should be, making the previous tests invalid.
Here is an illustration:

Date Information Valid?
12.15.08 xyz NO
12.16.08 mno NO
12.17.08 ididi No
12.18.08 ddidj No
12.19.08 ddidi Yes
Dec 19 '08 #25

FishVal
Expert 2.5K+
P: 2,653
Are the results stored in s table?
If so, then, please, post the table metadata.
Dec 19 '08 #26

100+
P: 166
Yes, the results are stored in a table, MCorrections
correctionsID PK Auto
DM_Mix Number, DM_MaterialNo (currently composite FK, tbl MixSample)
corrDt Date- Date moisture of material was measured
corrDryGs Number - Material Dry Grams material measured
corrWetGs Number- Material Wet Grams material measured
corrAbsorption Number- Material water absorption
Dec 19 '08 #27

FishVal
Expert 2.5K+
P: 2,653
As I've stated already, relate the table to [Material] table by replacing current FK to FK(Material).
Dec 19 '08 #28

100+
P: 166
Fish,

Please explain why you think it should be in the materials table. I think it should not because then it would just be related to the materials in general, and not the materials related to the MixSample.

I tried to implement the changes you suggested, and I was unable to get it working.

Here is my table set up:
MixDesign:
DM_Mix
DM_SampleNO
DM_Date...etc

Material:
materialID
matTypeID
material
materialGrav

MCorrections: (Composite Table)
correctionsID PK Auto
DM_Mix Number (FK, tbl MixDesign)
materialID Number (FK, tbl Material)
corrDt Date- Date moisture of material was measured
corrDryGs Number - Material Dry Grams material measured
corrWetGs Number- Material Wet Grams material measured
corrAbsorption Number- Material water absorption

I have been unable to get the value for MCorrections.materialID to update in the table.
Dec 19 '08 #29

FishVal
Expert 2.5K+
P: 2,653
Ok.

Lets outline the situation.
  • One material could be used in preparation of multiple samples. This is reflected in your design with FK(Material) in [MixSample] table.
  • Next, you state that one material could have multiple results of whatever test. This obviously leads us to a table (for test results storing) related to [Material] as many-to-one. The table includes [IsValid] field which stores user's decision as for whether the test result valid or not.
    Now questions:
    • Is there only one result expected to be valid for a given material throughout of its shelflife?
    • Are there expected to be multiple valid results for a single materail, for example - material has to be retested after a certain period of time?
    • If so, then is there any definite logic to choose a relevant result for a given material in a given sample, for example - the most recent result for a given sample?
    • Is a user supposed to make a final decision as for what result is relevant for a given sample preparation. If so then you will need to replace [materialID] in [MixSample] table with PK of the table storing test results.
Regards,
Fish
Dec 21 '08 #30

100+
P: 166
"One material could be used in preparation of multiple samples. This is reflected in your design with FK(Material) in [MixSample] table."
>>True
"Next, you state that one material could have multiple results of whatever test. This obviously leads us to a table (for test results storing) related to [Material] as many-to-one. The table includes [IsValid] field which stores user's decision as for whether the test result valid or not."
>>Yes, one material could have multiple results related to the material corrections test...but in my mind these materials are not the materials in general, per se, but the materials that have been selected to be a part of the mix sample.
"Now questions: Is there only one result expected to be valid for a given material throughout of its shelflife?"
>> It's shelflife? We use the materials like ingredients for cooking. You purchase a whole pint of milk, but you only use a certain amount for cooking. Tests for each material Type 2 and 3 will be ran until they reason what they are supposed to be. These tests will be done every 24 hours (the most I've seen done is 10 tests) until they reach the correct consistency.
"Are there expected to be multiple valid results for a single materail, for example - material has to be retested after a certain period of time?
>>The 'single material' would be apart of the materials that were used to create the mix (these materials exist in the MixSample table). How ever much material is used in the mix, is the amount that will be tested, every 24 hours until it reaches the right consistency.
"If so, then is there any definite logic to choose a relevant result for a given material in a given sample, for example - the most recent result for a given sample?"
>>In my MCorrections table, I allow for many results for each Mix, and I use a corrDt and an isValid checkbox. The date tells when (I may incorporate time as well) the test was done and the check box tells whether the last test is valid...in my mind when the correct consistency is reached, there will only be one valid record, and one check box checked...
"Is a user supposed to make a final decision as for what result is relevant for a given sample preparation. If so then you will need to replace [materialID] in [MixSample] table with PK of the table storing test results."
>>The user makes the final decision about the consistency of the material (the materials have already been selected and stored in the MixSample table). If I take out the materialID in the MixSample table, then the user will not be able to record the materials that create the MixSample. In the MCorrections, I am storing the information related to the tests...but these results are related to the MixSample table.
One MixSample material will have one or more moisture correction tests performed.
One or more moisture correction tests will be preformed on one or more MixSample materials.
Dec 22 '08 #31

FishVal
Expert 2.5K+
P: 2,653
@csolomon
That "selection of material to be a part of mix sample" means only that the same material appears in multiple records of [MixSample] table.
The following will be rephrase of the question I've been asking you multiple times, but since you disagree with my point from some reasons unclear for me so far I will ask it again.

Could it be so that different records in [MixSample] table with the same value of FK([Material]) are related to different "valid" results of the material testing?
  • If it isn't so and a single valid result (which BTW could change) for a given material has to be related to all [MixSample] records related to this given material, then the scheme I've suggested
    [MixSample] Mto1 [Material] 1toM [MaterialCorrections]
    is a right one.
  • If it is so, then you should elaborate on how multiple eventually valid results are being obtained to let me suggest you consistent table structure.

@csolomon
This certainly makes my 2nd point in previous answer irrelevant.
However, I wouldn't take it out since you are very persistent trying to relate table the way you like. :)

@csolomon
:) This again eliminates possibility for multiple "valid" results for a given material.
Do you still want to relate [MixSamples] to material corrections table?

@csolomon
What is the reason to store key combination which is a subject to be invalidated with future state of table?
The logic seems to be straightforward - valid record of test result for a given material is marked with True value in [IsValid] field. [MisSample] table could be joined with record in material corrections table on the following criteria:
[MixSample].[materialID]=[material corrections table].[materialID]
and fetched records has to be only those where [material corrections table].[IsValid]=True.
This creates dynamic relation.

@csolomon
This leave me wondering - what all this was about. :)

Regards,
Fish
Dec 22 '08 #32

100+
P: 166
Hi Fish,

I apologize for not being able to answer your question clearly.

Could it be so that different records in [MixSample] table with the same value of FK([Material]) are related to different "valid" results of the material testing?" >>I believe this statement is true.

The results are stored in the MCorrections table. I am unsure how to clearly state the validity of the test results. So on day 1 when they run the tests (test1), if they are at the correct moisture, there will be no more tests...if not, they preform the test (test2) 24 hours later...which will make test1 invalid and 2 valid until they discover whether or not they need to test again. If they do, then test3 will invalidate test2..

"This certainly makes my 2nd point in previous answer irrelevant.
However, I wouldn't take it out since you are very persistent trying to relate table the way you like. :)"
>>It's not that I want to relate the table the way I'd like; I'd like for my tables to be correctly set up so that my design will work. I am open to your suggestions, but i may not understand them as I see them written here and taking in to consideration how the process works, it may not appear to be correct...So the more I explain it to you, I feel the more you will understand the process and can assist in reaching the goal of the correct design. I really appreciate your time and input.

":) This again eliminates possibility for multiple "valid" results for a given material. Do you still want to relate [MixSamples] to material corrections table?"
>>I did not mean to suggest that there were multiple valid results; ultimately only one result will be valid...the last one performed.

Here is how I have it currently set up:
[MixSample] Mto1 [Material] 1toM [MaterialCorrections]

"What is the reason to store key combination which is a subject to be invalidated with future state of table?"
>>Key combination?--Do you mean the composite key that used to exist? I have since created one single PK and just used my FKs.

"The logic seems to be straightforward - valid record of test result for a given material is marked with True value in [IsValid] field. [MisSample] table could be joined with record in material corrections table on the following criteria:
[MixSample].[materialID]=[material corrections table].[materialID]
and fetched records has to be only those where [material corrections table].[IsValid]=True.
This creates dynamic relation."
>>The issue with this is that when I try to do this: [MixSample].[materialID]=[material corrections table].[materialID], access can not determine the relationship.
I completely agree that if I want this to relationship to work that I need the MixSample.MaterialID =MCorrections.MaterialID relationship to work.
Dec 22 '08 #33

100+
P: 166
Hi Fish,

I have been playing around with the design:
MixSample] Mto1 [Material] 1toM [MaterialCorrections]

Here are the tables:
TblMixSample:
DM_MaterialNo- PK
DM_Mix-FK, MixDesign
materialID-FK Material
matTypeID-FK MatType
matBatchWeight
pigPercent

tblMaterial:
materialID PK
matTypeID
material
materialGrav

tblMCorrections
correctionsID-PK
materialID-FK Material
DM_Mix-FK MixDesign
corrDt-Day the test was done
corrDryGs- the weight of each material Dry
corrWetGs- the weight of each material Wet
corrAbsorption- the amount of water absorption of each material
corrValid- checks to determine which results are valid

I am using a continuous form, who's control source is the MCorrections table. what I have noticed is:
1) All of the records, regardless of the sample number are on one page.
I realize this is because of the continuous form. I could change to the single form, which I have but I am not satisfied because the user will have to scroll to see relevant records to do their tests. Is it not possible to just have all the records related to the one Sample number on one page, and add new records each time a new test is performed? I thought I could do this by having an unbound combo box with the MixDesign.DM_Mix = MixSample.DM_Mix and have the user select the sample number, and populate the materialID combo box with the correct results relating the the sample number selected.
Dec 22 '08 #34

FishVal
Expert 2.5K+
P: 2,653
Hello, solomon.

@csolomon
Ok. I guess talking about "relationship" we could mean completely different things.

Relationship is a special rule in database design to keep data consistent by applying definite constraints to values of fields in PK/FK couple.
Being such it should be treated as such - this means the only purpose of relationship is to keep data consistent, relationships in database could "coincide" with what you are considering to be relationship but they are certainly not obliged to do this.

Below is an example of table design and query to retrieve data.

TblMixSample:
DM_MaterialNo- PK
materialID-FK Material
<... all the rest fields ....>

tblMaterial:
materialID PK
matTypeID
material
materialGrav

tblMCorrections
correctionsID-PK
materialID-FK Material
DM_Mix-FK MixDesign
corrDt
corrDryGs
corrWetGs
corrAbsorption
corrValid

Expand|Select|Wrap|Line Numbers
  1. SELECT TblMixSample.*, tblMCorrections.* FROM TblMixSample LEFT JOIN tblMCorrections ON TblMixSample.materialID=tblMCorrections.materialID WHERE tblMCorrections.corrValid;
  2.  
Regards,
Fish
Dec 23 '08 #35

FishVal
Expert 2.5K+
P: 2,653
@csolomon
You still have FK(MixDesign) field in your table. :)
That means - there are multiple valid results of material testing (I mean they are valid permanently - not that the next result will invalidate the previous).
Really - record in this table says: "correction for material X used in sample Y has the following parameters", which means material X could have different correction parameters when used to prepare sample Z, which means material X has at least 2 different permanently valid correction parameters sets.


It is not an uncommon situation, thats why I've asked you several times whether a single material could have multiple permanently valid test results, but from all you've explained I concluded that you expect for only one permanently valid result for a single material.

So we have two options:
  • You kill tblMCorrections.DM_Mix field to death.
  • You tell me something I havn't realized so far to convince me that this field is needed. :)

Regards,
Fish
Dec 23 '08 #36

100+
P: 166
A single material could have multiple permanent valid test results, as long as it pertained to different MixSamples (DM_Mix).

I have 'killed tblMCorrections.DM_Mix field to death' :) and here is my table design:
tblMCorrections
correctionsID-PK
materialID-FK Material
corrDt-Day the test was done
corrDryGs- the weight of each material Dry
corrWetGs- the weight of each material Wet
corrAbsorption- the amount of water absorption of each material
corrValid- checks to determine which results are valid

I tried to connect MixSample.MaterialId to MCorrections.materialID and access cannot determine the relationship type.
Dec 23 '08 #37

FishVal
Expert 2.5K+
P: 2,653
@csolomon
:D

Now, when you amost convinced me that single materail is expected to have a single permanent valid result, it appears that it isn't so.
Could you elaborate please on how these results are being obtained.
My guess was - material is a subject for multiple tests (as a test here I assume a sequence of testings finally giving valid result).
If so, then this will require two tables related as 1toM:
tblMTests - each record is a sequence giving finally valid result
tblMTestMeasurements - each record is a measurement result (valid or not)

Something like the following:
[Material] 1toM [tblMTests] 1toM [tblMTestMeasurements]
PK of tblMTests should be used instead of [materialID] in [TblMixSample].
[tblMTests] 1toM [TblMixSample].

However, I'm still not sure I understand you completely. :)

@csolomon
As I've posted before - relationship is a database rule to keep data consistent.
It is like a marriage and what you are mentioning is more like free love. :D
Don't mess them.

Regards
Fish
Dec 23 '08 #38

100+
P: 166
"Now, when you amost convinced me that single materail is expected to have a single permanent valid result, it appears that it isn't so."
--I just think we are understanding what the other is conveying now :D

The 'free love' portion, I got from you :) here:
"The logic seems to be straightforward - valid record of test result for a given material is marked with True value in [IsValid] field. [MisSample] table could be joined with record in material corrections table on the following criteria:
[MixSample].[materialID]=[material corrections table].[materialID]
and fetched records has to be only those where [material corrections table].[IsValid]=True.
This creates dynamic relation." Maybe that's not what you meant?

"Could you elaborate please on how these results are being obtained."
>>Really, only certain things are being recorded during the tests; the date, water absorption, the weight of the material wet and the weight of the material dry...based on these inputs, (and the materialBatchWeight which is stored already) the rest of the information related to it will be calculated. A material will be the subject of tests until the correct measurements are reached. Tests done prior to the correct measurements are not valid.

"Something like the following:
[Material] 1toM [tblMTests] 1toM [tblMTestMeasurements]"
>>I think this relationship could be correct if you mean that one material will have many tests (true) .
One test will have many measurements, meaning measured many times, for many different mixes
"PK of tblMTests should be used instead of [materialID] in [TblMixSample].
[tblMTests] 1toM [TblMixSample]."
>> :) Here is where I am unsure...materialID in TblMixSample is what allows the user to determine what materials are being used to create the Mix; if we take it out, how will the user select the materials?
Dec 23 '08 #39

FishVal
Expert 2.5K+
P: 2,653
@csolomon
Well. :)

I suppose, from all you've told me about business logic of your application, I could conclude the following:
  • 1. Tests are performed on materials, not on mixed samples.
  • 2. Test result is being obtained multiple times within single test.
  • 3. Obtained result (usually last but generally that marked as valid) within single test is supposed to be temporarily valid until it is invalidated by the next valid one.
  • 4. Eventually test gives result that is concidered to be valid permanently and the test is finished. By "permanently" I mean - this result will be valid forever for mixsamples it was assigned to regardless of ... see below.
  • 5. The test (as it is described in 2-4) could be repeated multiple times for the same material, thus giving multiple "permanent results" which are assigned to different sample mixes.

Kind regards,
Fish.
Dec 23 '08 #40

100+
P: 166
Fish:

1. Tests are performed on materials, not on mixed samples.
>>NO! This is what I thought you thought, so I was trying to make clear that they were NOT performed on materials in general, but the materials used, the coarse and fine, to create the mixed samples, stored in the MixSample table
2. Test result is being obtained multiple times within single test.
>>HRM...Well I look at it as a single test is being performed multiple times until the correct level is reached
3. Obtained result (usually last but generally that marked as valid) within single test is supposed to be temporarily valid until it is invalidated by the next valid one.
>>I agree with this one
4. Eventually test gives result that is concidered to be valid permanently and the test is finished. By "permanently" I mean - this result will be valid forever for mixsamples it was assigned to regardless of ... see below.
>>True
5. The test (as it is described in 2-4) could be repeated multiple times for the same material, thus giving multiple "permanent results" which are assigned to different sample mixes.
>>True
Dec 23 '08 #41

FishVal
Expert 2.5K+
P: 2,653
@csolomon
This requires translation. :D

What does it mean "NOT performed on materials in general, but the materials used, the coarse and fine, to create the mixed samples, stored in the MixSample table".
Is that not the same materials stored it [Material] table?
Materials used to prepare mixed samples are subset of materials stored in [Material] table, aren't they?
Dec 23 '08 #42

100+
P: 166
Yes they are a subset of the materials in the Materials table. I just thought that the relationship was between the materials in the MixSample table as opposed to those in the materials table...I know it sounds confusing, but it is confusing to me as well as to why the link is between MCorrections and Materials as opposed to MCorrections and MixSamples.

When I link them like this:
Materials to MCorrections (1TOM)
Materials to MixSample (1TOM)
it doesn't work. I know you said I should have a relationship with MixSample.materialID and MCorrections.materialID, but access cannot determine that relationship.
Dec 23 '08 #43

FishVal
Expert 2.5K+
P: 2,653
Sure Access cannot determine relation between MixSample.materialID and MCorrections.materialID because neither field is unique.
Really, relationship is [MCorrections] 1toM [MixSample].
This require FK([MCorrections]) in [MixSample] table.
  • If all materials used in mixed sample preparation are expected to have result of moisture test, then FK([MCorrections]) field should be used instead of FK([Material]) table. Really, FK([MCorrections]) unambigiously determines material.
  • If not all materials used in mixed sample preparation are expected to have result of moisture test, then FK([MCorrections]) field should be used together with of FK([Material]) table or an additional table should be created to relate [MCorrections] records to [MixSample] records - a kind of Many-to-Many "bridge" table though not really the same.

    [tblMixSampleCorrection]
    <fieldname> FK([MixSample])
    <fieldname> FK([MCorrections])

    Not really the same because not all key combinations are allowed, but only those related to the same material.

[MCorrections] table has to be splitted to two tables related as 1toM:
table for tests and table for test measurements - if you feel more convinient use other terms, then tell me please what names would you prefer for these entities. :)
Thus valid result will have constant value of PK - PK of table for tests.
Value of valid result will be retrieved with a query similar to that I've posted previously.
Dec 24 '08 #44

100+
P: 166
Fish,

"Really, relationship is [MCorrections] 1toM [MixSample].
This require FK([MCorrections]) in [MixSample] table."
--I have put the FK(MCorrections) in the MixSample table, but I am not sure it is necessary
"If not all materials used in mixed sample preparation are expected to have result of moisture test, then FK([MCorrections]) field should be used together with of FK([Material]) table or an additional table should be created to relate [MCorrections] records to [MixSample] records - a kind of Many-to-Many "bridge" table though not really the same."
>>I agree with this. I decided to have an additional table to relate [MCorrections] records to [MixSample] records

tblMixSampleCorrections: (composite of MixSample and MCorrections)
DM_MaterialNo- FK([MixSample])
corrections- FK([MCorrections])

I have also split my MCorrections table into two tables:
MCorrections:
correctionsID-PK
corrDt- Date
corrValid- tells whether or not the record is valid

MCTests
MC_TestID- PK Auto
correctionsID- FK Number, tblMCorrections
corrDryGs
corrWetGs
corrAbsorption

So here are my relationships:

1 To M To 1
MixSample to MixSampleCorrections to MCorrections
and MCorrections to MCTests

I am not sure about this design as far as how I can get my forms set up so that the user can input a test related to a MixSample.
Dec 24 '08 #45

FishVal
Expert 2.5K+
P: 2,653
  • [corrValid] field should be in [MCTests] table to mark valid record
  • [corrAbsorption] field is, IMHO, redundant as soon as its value could be calculated from [corrDryGs] and [corrWetGs], isn't it?
  • I don't know how the test is performed exactly, but I guess [corrDryGs] should be in [MCorrections] table if a sample under test is weighed when test started to give "dry" weight and subsequent measurements are "wet" weight until the sample reaches saturation.
Dec 24 '08 #46

100+
P: 166
Season's Greetings,

"[corrAbsorption] field is, IMHO, redundant as soon as its value could be calculated from [corrDryGs] and [corrWetGs], isn't it?"
>>corrAbsorption is an input value. It along with corrDryGs and corrWetGs are what is used to create 7 other calculated values.

I understood the results related to the tests (calculated values) should not be stored...instead, I would store the items that contributed to the calculated values, in this case, corrAbsorption, corrDryGs and corrWetGs. These items are stored in the MCTests table.

I have been working with the design, but I do not have any results.
Dec 25 '08 #47

100+
P: 166
Hi Fish,

To give you a better picture of what my current design looks like, I have posted a picture of the ERD.
Dec 29 '08 #48

FishVal
Expert 2.5K+
P: 2,653
Hello, csolomon.
  • [MCorrections] table has no relation to [Material] table. Why?
  • [MCTests] table has DM_MaterialNo - FK(MixSample) ??? What for?
  • [MixSampleCorrections].[DM_MaterialNo] for some unknown reason is FK of [MCorrections].

Kind regards,
Fish.
Dec 29 '08 #49

100+
P: 166
Hi Fish,

[MixSampleCorrections].[DM_MaterialNo] for some unknown reason is FK of [MCorrections].
--Earlier in our conversation, you suggested:
"If not all materials used in mixed sample preparation are expected to have result of moisture test, then FK([MCorrections]) field should be used together with of FK([Material]) table or an additional table should be created to relate [MCorrections] records to [MixSample] records - a kind of Many-to-Many "bridge" table though not really the same."
--To which I replied:
>>I agree with this. I decided to have an additional table to relate [MCorrections] records to [MixSample] records

tblMixSampleCorrections: (composite of MixSample and MCorrections)
DM_MaterialNo- FK([MixSample])
corrections- FK([MCorrections])"

--This is how I got the MixSampleCorrections table with the DM_MaterialNo and CorrectionsID. Maybe I interpreted what you said incorrectly?

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.
Dec 30 '08 #50

54 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.