Displaying Controls on Forms and Reports

N

Nick Bradbury

Hi

I am building a database to input results for laboratory samples and I want,
if possible, to design one generic form that will display the fields
relevant for each test. For example TestQC1 has five stages Y&M, TVC37,
TVC22, PS and Ecoli, TestQC2 has three stages TVC30, PS and Coliforms. In
the table called TestSuites the fields are

TestSuiteID - Autonumber
LabNo - Long Integer
TestSuite - Text [Indexed]
Description - Text
Status - Yes/No
Stage1- Text
Stage2 - Text
Stage3 - Text
Stage4 - Text
Stage5 - Text

I dont want to create a form for each if it can be avoided as there are a
lot of test suites, is this feasible or do I need to consider a seperate
table for the Stages

Thanks

Nick
 
A

Allen Browne

One sample can have multiple tests.
One test can have mulitple stages.
Those both imply one-to-many relationships.

I assume that one test usually consists of the same stages in the same
sequence? If so, it makes sense to teach the database about that, so you
will have tables like this:

Test table (one record for each kind of test you conduct) with fields:
TestID primary key
TestName Text

TestStage table (one record for each stage of each test). Fields:
TestStageID primary key
TestStageName Text
TestID foreign key to Test.TestID
Sequence Number 1 is first stage of test, 2 = next, ...

Sample table (one record for each sample received for testing):
SampleID primary key
ClientID who this test is for
RecdDate when the sample was received for testing.

SampleTest table: one record for each test requested on each sample.
SampleID which sample is to be tested
TestID which test the sample is to have.

SampleTestStage: one record for each stage of each test of each sample.
SampleID which sample was tested.
TestStageID which test stage has been conducted.
EmployeeID who supervised the test.
Result result of this test stage for this sample.
 
N

Nick Bradbury

Hi Allen

Thanks for that it has made the structure much clearer.

Nick
Allen Browne said:
One sample can have multiple tests.
One test can have mulitple stages.
Those both imply one-to-many relationships.

I assume that one test usually consists of the same stages in the same
sequence? If so, it makes sense to teach the database about that, so you
will have tables like this:

Test table (one record for each kind of test you conduct) with fields:
TestID primary key
TestName Text

TestStage table (one record for each stage of each test). Fields:
TestStageID primary key
TestStageName Text
TestID foreign key to Test.TestID
Sequence Number 1 is first stage of test, 2 = next, ...

Sample table (one record for each sample received for testing):
SampleID primary key
ClientID who this test is for
RecdDate when the sample was received for testing.

SampleTest table: one record for each test requested on each sample.
SampleID which sample is to be tested
TestID which test the sample is to have.

SampleTestStage: one record for each stage of each test of each sample.
SampleID which sample was tested.
TestStageID which test stage has been conducted.
EmployeeID who supervised the test.
Result result of this test stage for this sample.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick Bradbury said:
I am building a database to input results for laboratory samples and I
want, if possible, to design one generic form that will display the
fields relevant for each test. For example TestQC1 has five stages Y&M,
TVC37, TVC22, PS and Ecoli, TestQC2 has three stages TVC30, PS and
Coliforms. In the table called TestSuites the fields are

TestSuiteID - Autonumber
LabNo - Long Integer
TestSuite - Text [Indexed]
Description - Text
Status - Yes/No
Stage1- Text
Stage2 - Text
Stage3 - Text
Stage4 - Text
Stage5 - Text

I dont want to create a form for each if it can be avoided as there are a
lot of test suites, is this feasible or do I need to consider a seperate
table for the Stages

Thanks

Nick
Click to expand...
Click to expand...
 
Top