How to Manage this type of Data?

[cap.zadi]

Hey there,



I would like to ask your kind advise for this case: I am using this relationship among 2 tables and data entry through Form and subform where "TestNo" is key for relationship.

TblT8

TestNo PK AutoNumber
TestDate
TechID CB:Value List
KilnNo CB:Value List
MtrlID CB:Value List
Note

TblRT8
ResultNo PK AutoNumber
TestNo FK
PrdDate
Top
Middle
Bottom


this relationship is working fantastic but now i need a third tble where need to add data link to Top, Middle & Bottom Values in each entry.

The fields for data required in 3rd table is only

W1 1st Weight
W2 2nd Weight



please advise how to manage this?


thanks

Zee

[jzwp11]

What do top, middle and bottom represent? To what do they refer?

[cap.zadi]

those are test readings in numerial values like 34.50 or 23.00.

Actally the procedure is: lab people took three sample pieces from a Pipe "Top", Middle and "Top" then do Weight-1 and then put the sample in water for a standard time and then again do weight-2 which is always higher than the 1st one and then do the required test which give up those readings of water absorption for each part Top and so on. hope it will be clear now.

thanks for ur time.

zee

[cap.zadi]

Sorry : "Top", "Middle", "Bottom"

[jzwp11]

So it sounds like top, middle and bottom are sample locations. Since you have many sample locations, that describes a one-to-many relationship. And then if each location has multiple data points that is another one-to-many relationship.

TblRT8
ResultNo PK AutoNumber
TestNo FK
PrdDate


tblSampleLocations
-pkSampleLocID primary key, autonumber
-fkResultNo foreign key to tblRT8
-fkLocationID foreign key to tblLocations

tblLocations (holds 3 records top, middle, bottom)
-pkLocationID primary key, autonumber
-txtLocation

SampleLocationWeights
-pkSampleLocWtID primary key, autonumber
-fkSampleLocID foreign key to tblResultLocations
-longWtNo ( weight 1 or 2 as you described)
-spWeightValue (the actual measured weight); a single precision number field based on the values you indicated (i.e. 34.50 or 23.00)

[cap.zadi]

Hey, have arrnage the tables as u advise and it seems working good. how to manage the form which can give easy data entry and hide the unnecessary fields.?

[cap.zadi]

Hey, please find here the attached tables.

[jzwp11]

I noticed in your database that you had several fields in your tables that were setup as value lists/combo boxes. Although Access has this capability, it is generally not recommended to have lookups (combo/list boxes) at the table level. The lookups are best left for forms. This site gives a detailed explanation as to why table level lookups are not recommended. I went ahead and created new tables to hold the pipes, kilns, people and weight numbers and established the appropriate relationships in the relationship window. The modified database is attached.

I did have one question that needs to be addressed before working on forms. Is there a relationship between the kilns and the pipes?

[cap.zadi]

Hey there, so nice of you for ur kind efforts and time.

Yes those pipes are loaded on cars and then pass through the Kilns for firing and then unloaded. i think i have post another enquiry today "Is right way to do" there is detail about this process and database is attached.

So there is a production process and then we have every day QC tests which are recorded in seperate databases but infact i am interested in future to combine both the databases and then monitor and track the results easily with refrence of QC reports.

like if i want to see the production results of Kiln Car No 20 and also what could be the QC results.

thanks

zee

[jzwp11]

Does a specific pipe always go through the same kiln? If so, then you do have a relationship and more work on your structure would be necessary; it not, then there is no relationship and your tables are properly structured.

So there is a production process and then we have every day QC tests which are recorded in seperate databases but infact i am interested in future to combine both the databases and then monitor and track the results easily with refrence of QC reports.

like if i want to see the production results of Kiln Car No 20 and also what could be the QC results.

This can be done with the proper table structure.

[cap.zadi]

well, thanks for ur special attention and concern. Its really a great explorer while talking to u over here.

No Pipes can be go through from any kiln.

I have another question here:

the main tables for Test8 are

tblT8
tblRT8
tblLT8
tblSLT8
tblSLWT8
tblweightnumbers

and these tables are supporting data;

tblKilns
Tblpeople
tblepipes

i have another tests where main table structure is different for each test but those supporting tables will be same, so can we use these tables inr relationship with other Tests structure?

please advise.

thanks

zee

[cap.zadi]

well, go through the article which is not recommending lookup fields in tables. so can you advise me how to handle this in my database? thanks

[jzwp11]

well, go through the article which is not recommending lookup fields in tables. so can you advise me how to handle this in my database? thanks

Instead of having lookups in the tables, you would put the data into their own tables as I have already done in the modified database I posted. You would use those tables to populate combo or list boxes on your forms.

i have another tests where main table structure is different for each test but those supporting tables will be same, so can we use these tables inr relationship with other Tests structure?

I am not sure I quite understand, but if you conduct other tests on the samples from the top/middle/bottom and then yes, you should be able to incorporate those tests with only minor changes in the structure. You will have to explain a little more about these tests and how they fit in with the water absorption test and with the samples and locations.

[cap.zadi]

well, thanks.

ok let me explain you. i have another test which is called crushing strength test which will be having the different main tables structures:

tblT13

pk testno autonumber
testdate
prddate
MtrlID
pipelength (Length of Pipes)
technid * EmpID

tblRT13

pk resultno autonumber
fk testno frm tblt13
crushingload reading (like: 123.34)
crushingstrength (* Crushingload/Pipelength)
standard (like for Pipe A=72, Pipe B= 60 so for each pipe different but fixed)
Result (if crushingstrength > Standard, passed else failed)

tblDM
pk dmno Autonumber
fk resultNo frm tblRT13
Length
width

Questions:

1- This test structure is running right now with me but not sure if have the right direction?
2. i have again use here the Value list for MtrlID and People so can we use those tables here as well?
3. I have already automate the results in query and reports using IFF function but still i enter manually Pipelength and standard values where as those values fixed for each pipe. so how we can fix them so can appear automatically.

so there are many other type of test which we do here.

please advise.

thanks.

[jzwp11]

First, you would not have a separate table structure for each test, so we need to integrate all of your tests into 1 structure.

2. i have again use here the Value list for MtrlID and People so can we use those tables here as well?

Yes, in order integrate, we will have to.

If the pipe length is fixed for each pipe, it is an attribute of the pipe and thus needs to be recorded in a field in tblPipe.

crushingstrength (* Crushingload/Pipelength)
Result (if crushingstrength > Standard, passed else failed)

The above 2 fields in your table are calculated values and thus should not be stored in a table at all. You would just calculate them on the fly in a query, form or report.

You do need to record the actual crushing load value since that is the raw data on which the above calculations are based.

The standard you refer to I assume is some sort of specification, you will need to put those values somewhere eventually.

Now, just to clarify things for me, I assume that you are doing the tests on the material in the pipe not on the pipe itself. Am I correct in my thinking? You may have to explain your actual manufacturing process a little.

What other tests do you run on the material? Are samples taken from specific locations (top/middle/bottom) for all tests or do some tests apply to the entire length of the material?