Table Structure - Linked Tables

[travisdh]

Hello,

I am slowly tip toeing into the world of Microsoft Access and am finding it quite interesting, however I have a strange table requirement that I am trying to get working and to be honest I am quite stumped at the easiest way to do this.

Basically the table is a 'test request' table, whereby a product is entered into the table and from there I would like to be able to select the test that the item needs to be tested for (multiple value lookup table).

This would not be a problem normally, however there are actually two parameters I would like to be able to link, one being the test being conducted, and the other being the time duration at which the test is being conducted.

e.g.

pH at 1 Days
pH at 2 Days
pH at 3 Days
eC at 1 Days
eC at 2 Days
eC at 3 Days

What is the best way to be able to define both the test, and the duration at which the test needs to be conducted, or should I just have the one test table, which has for example EC 1D, EC 2D, EC 3D, PH 1D, PH 2D, PH 3D

Id like to avoid the one table if i could, as having multiple tables would give me more flexibility to 'mix and match' durations, but I am not really sure if it is possible.

Thanks for the tips

 

[Pat Hartman]

I don't have enough information but this sounds like a typical many-to-many relationship.
You have something which needs testing (you havent said what), you have tests which need to be performed and the interval at which they are performed, and finally, you would have a table to hold the results (you haven't mentioned this either). Is the pH test always the same test regardless of on which day it is performed? Are some tests performed once and others performed on multiple days? Given the sketchy information provided, I would say that you would not define days when you define the test but you would add the days information when you record the results.

 

[Ronjing]

The amount of information scarce so I will reply with vague answers. Is this really a table requirement or could this just be 2 combo boxes on a form? You can enter the validation rules for a test type and test duration. If you do want to use a table, I might suggest creating 2 tables and bring them together in a union query. Just a few ideas from what I am seeing though more information would be useful.

 

[travisdh]

Sorry about the lack of information, I was not really sure what information was needed. The items being tested vary, but I thought i would just refer to the as "Product" which would go into the sample table, from there I wanted to be able to define what each product is being tested for, and for each test what duration(s) should the tests be conducted at.

One test could have multiple durations, or one duration could have multiple tests, ideally though having it in some form of table would be useful so that I could generate an 'upcoming tests' report to show for each sample, what tests are needed at what durations.

There would be a table to hold the results, if that linked to the sample table, and a duration table, would I be able to generate a report if no results were in the table, and how would it handle a potentially unlimited number of variations (in reality not unlimited, but also too much to hand code each field for Day 1, Day 2 etc.

The test would also not always be the same at each duration, pH on one sample may be conducted at 5 days, or 20 on another, or there may not be pH and another test may be conducted at 2 days, or 30 days etc.

 

[travisdh]

I think it would need to be a table, i will give you a bigger picture idea of what i am trying to accomplish in the hope it might make it clearer. Basically i am trying to create a database which tracks client samples that are received for testing, so that when the samples are recieved they are entered into the ClientSamples table, this table would store information about the sample (e.g. when it was received, what sort of sample it is, and most importantly what tests the sample needs conducted on it (and at what duration), the idea being someone could enter that the sample needs to be tested for pH, (or any other test in the system) and what time, and this would be stored in the database. Having this information in the database would then allow Lab people to generate a report that shows what tests the samples need at what times.

I would think with the report, it would require data to be in the database to be able to look up what they need to test.

Finally the technicians would conduct their tests, and then at the end enter the results in a database (results table).