- [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.
-
-
Season's Greetings,
"[corrAbsorption] field is, IMHO, redundant as soon as its value could be calculated from [corrDryGs] and [corrWetGs], isn't it?"
>>corrAbsorptio n 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...instea d, 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.Comment
-
Hi Fish,
To give you a better picture of what my current design looks like, I have posted a picture of the ERD.Comment
-
Hello, csolomon.
- [MCorrections] table has no relation to [Material] table. Why?
- [MCTests] table has DM_MaterialNo - FK(MixSample) ??? What for?
- [MixSampleCorrec tions].[DM_MaterialNo] for some unknown reason is FK of [MCorrections].
Kind regards,
Fish.Comment
-
Hi Fish,
[MixSampleCorrec tions].[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
tblMixSampleCor rections: (composite of MixSample and MCorrections)
DM_MaterialNo- FK([MixSample])
corrections- FK([MCorrections])"
--This is how I got the MixSampleCorrec tions 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.Comment
-
Hello, csolomon.
Now the relation looks fine - MixSampleCorrec tions.CorrectionID is FK(MCorrections ).
Ok. Tables structure looks quite good but the one thing, which is mostly my fault. :(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.
[MixSample] relates to [MixSampleCorrec tions] 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 MixSampleCorrec tions.DM_Materi alNo primary key. This, BTW, means that MixSampleCorrec tions.correctio nsID 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 [MixSampleCorrec tions] 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:
[qryValidCorrect ions]
Code: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
[qryMixSampleMat erialsCorrectio ns]
Code: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.
Code:SELECT MixSample.*, qryMixSampleMaterialsCorrections.* FROM MixSample LEFT JOIN qryMixSampleMaterialsCorrections ON MixSample.DM_MaterialNo = qryMixSampleMaterialsCorrections.DM_MaterialNo;
Regards,
FishComment
-
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 MixSampleCorrec tions.DM_Materi alNo 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_Sa mpleNo FROM MixDesign;
which gives me a list of all Sample Numbers available
And this query for the second combo box:
SELECT Material.materi alID, Material.materi al, MixSample.DM_Ma terialNo, MixDesign.DM_Sa mpleNo FROM MixDesign INNER JOIN (Material INNER JOIN MixSample ON Material.materi alID = MixSample.mater ialID) ON MixDesign.DM_Mi x = MixSample.DM_Mi x WHERE (((Material.mat erialID)=[MixSample].[materialID]) AND ((MixDesign.DM_ SampleNo)=[Forms]![MCorrections]![cboSampNo]) AND ((Material.matT ypeID)
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.ma terialID 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
[qryMixSampleMat erialsCorrectio ns]"
--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.Comment
-
I mean correction assigned by user to a given material in a given mixsample chosen from several possible.
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". :)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_Sa mpleNo FROM MixDesign;
which gives me a list of all Sample Numbers available
And this query for the second combo box:
SELECT Material.materi alID, Material.materi al, MixSample.DM_Ma terialNo, MixDesign.DM_Sa mpleNo FROM MixDesign INNER JOIN (Material INNER JOIN MixSample ON Material.materi alID = MixSample.mater ialID) ON MixDesign.DM_Mi x = MixSample.DM_Mi x WHERE (((Material.mat erialID)=[MixSample].[materialID]) AND ((MixDesign.DM_ SampleNo)=[Forms]![MCorrections]![cboSampNo]) AND ((Material.matT ypeID)
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.ma terialID 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.
What table is not populated and what "didn't work when you put it on the same form"? :)"To join the list above with relevant sample materials
[qryMixSampleMat erialsCorrectio ns]"
--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.
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,
FishComment
-
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 (tblMixsampleCo rrections). 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 (tblMixsampleCo rrections, 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 MixSampleCorrec tions 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.Comment
-
Hi Fish,
I have been working to sift through the problem of getting the MixSampleCorrec tions 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.
ChanellComment
Comment