Access relationships

[giovetti]

Hi guys,
I am trying to build a database but got stuck. Dunno much about it.
I want to my database to have samples, and those samples are put through a test which are run every day or twice a day.
First I want to be able to put a result on the test and depending on the test it should trigger another test or a repeat test and flag the sample so when i run a query that sample comes up again. I want to be able to trace down all tests and results obtained on every single sample.
I am not sure how to go about this, if i do it all in one table with multiple columns for the tests , like test1, test2, repeatTest1, repeatTest2... And the same for the runs.
A run is done twice a day or more depending on how many samples I obtain.

My tables would be like this
Sample Table
SampleID SampleNr Test



Test Table
TestID Test SampleNr

Run Table
RunID RunNumber Date Test

I am confused about the relationships in order to make it work.
Can somebody help Please I thought it was going to be easy but couldnt make it work.
Cheers

[jzwp11]

I had a few questions so that I could better understand your application.

Can the same test be conducted on multiple samples?
Can multiple tests be conducted on the same sample?
Does a test conducted on a sample only have 1 result or many results?

[giovetti]

I had a few questions so that I could better understand your application.

Can the same test be conducted on multiple samples?
A>YES
Can multiple tests be conducted on the same sample?
A>YES
Does a test conducted on a sample only have 1 result or many results? A>One result per test, many possible results.

Basically:
a particular run will have only one test at any one time but many possible tests to choose from, in fact the run number will be the test id or code joined with an auto incremented run number to keep count of the run type and count of total runs joined by the date.

One run- one test - many samples

One sample will have many tests so can appear in different runs loads of times but will appear on a particular run only once.

One sample- many runs- many tests- many results

A test can be repeated on a sample many times on consequent runs
One result per test but a test can have many results

Thanks for your help and the prompty reply

[jzwp11]

Just looking at the samples and tests, this is how I would structure the tables

tblSamples
-pkSampleID primary key, autonumber
-SampleNumber
-SampleName
other fields related to the sample

tblTests
-pkTestID primary key, autonumber
-txtTestName
other fields related to the test

Now associate the samples with the tests that need to be conducted

tblSampleTests
-pkSampleTestID primary key, autonumber
-fkSampleID foreign key to tblSamples
-fkTestID foreign key to tblTests
-Resultfield

I was a little confused by your explanation of the runs. Since a run can involve many samples but only 1 test per run, I think this may be what you are after.

tblRuns
-pkRunID primary key, autonumber
-dteRun (run date)


tblRunSampleTests
-pkRunSampleTestID primary key, autonumber
-fkRunID foreign key to tblRuns
-fkSampleTestID foreign key to tblSampleTests

[giovetti]

Thanks for this, will give it a go and let you know in due course.
Cheers