Database Structure

[jaf893@gmail.com]

I have a series of test results with each result consisiting of 10 test
conditions, 5 location conditions and then 3 test results. The table
looks a bit like this: (T = test condition, L = location condition, R =
test results)

T1 T2 .. T10 L1 L2 .. L5 R1 R2 R3
xx xx xx xx xx xx xx xx xx
xx xx xx xx xx xx xx xx xx
xx xx xx xx xx xx xx xx xx
xx xx xx xx xx xx xx xx xx
xx xx xx xx xx xx xx xx xx
xx xx xx xx xx xx xx xx xx
... .. .. .. .. .. .. .. ..
... .. .. .. .. .. .. .. ..
... .. .. .. .. .. .. .. ..
... .. .. .. .. .. .. .. ..
xx xx xx xx xx xx xx xx xx

I would like to have the test conditions, location conditions and test
results stored in separate tables. However, I am a complete beginner
with Access so could somebody please suggest what the best way to do
this would be?

[PC Datasheet]

Re: Database Structure

It seems that what you have is very good! Why do you want them in separate
tables?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

<jaf893@gmail.c om> wrote in message
news:1102001765 .654726.90450@c 13g2000cwb.goog legroups.com...[color=blue]
> I have a series of test results with each result consisiting of 10 test
> conditions, 5 location conditions and then 3 test results. The table
> looks a bit like this: (T = test condition, L = location condition, R =
> test results)
>
> T1 T2 .. T10 L1 L2 .. L5 R1 R2 R3
> xx xx xx xx xx xx xx xx xx
> xx xx xx xx xx xx xx xx xx
> xx xx xx xx xx xx xx xx xx
> xx xx xx xx xx xx xx xx xx
> xx xx xx xx xx xx xx xx xx
> xx xx xx xx xx xx xx xx xx
> .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. ..
> xx xx xx xx xx xx xx xx xx
>
> I would like to have the test conditions, location conditions and test
> results stored in separate tables. However, I am a complete beginner
> with Access so could somebody please suggest what the best way to do
> this would be?
>[/color]

[Joe Farish]

Re: Database Structure

I want them in seperate tables because there is a lot of data and it is
part of a larger project. Each set of results is a 171 line text file.
The test conditions are the same for every line so there is a lot of
redundancy so by separating them into different tables I'm hoping to
make it easier to reduce the redundancy. I might do this using lookup
tables but I've heard bad things about them!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bruce Dodds]

Re: Database Structure

PC Datasheet wrote:[color=blue]
> It seems that what you have is very good! Why do you want them in separate
> tables?
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> resource@pcdata sheet.com
> www.pcdatasheet.com
>
>
> <jaf893@gmail.c om> wrote in message
> news:1102001765 .654726.90450@c 13g2000cwb.goog legroups.com...
>[color=green]
>>I have a series of test results with each result consisiting of 10 test
>>conditions, 5 location conditions and then 3 test results. The table
>>looks a bit like this: (T = test condition, L = location condition, R =
>>test results)
>>
>>T1 T2 .. T10 L1 L2 .. L5 R1 R2 R3
>>xx xx xx xx xx xx xx xx xx
>>xx xx xx xx xx xx xx xx xx
>>xx xx xx xx xx xx xx xx xx
>>xx xx xx xx xx xx xx xx xx
>>xx xx xx xx xx xx xx xx xx
>>xx xx xx xx xx xx xx xx xx
>>.. .. .. .. .. .. .. .. ..
>>.. .. .. .. .. .. .. .. ..
>>.. .. .. .. .. .. .. .. ..
>>.. .. .. .. .. .. .. .. ..
>>xx xx xx xx xx xx xx xx xx
>>
>>I would like to have the test conditions, location conditions and test
>>results stored in separate tables. However, I am a complete beginner
>>with Access so could somebody please suggest what the best way to do
>>this would be?
>>[/color]
>
>
>[/color]

I can think of a number of reasons, including the ability to easily
tabulate and analyze test results.

You could set up three tables - test condition; location condition; and
test results. The test and location condition tables must have primary
keys. The test results table would have a compound primary key made up
of the primary keys of the test and location tables. Example
Test Results Table records:

Key: R1: R2: R3:
T1L1 xx xx xx
T1L2 xx xx xx
.. . .

This will work if all of the locations always will have the exactly the
same test conditions, and the number of test results are fixed.

In working with this structure you will need to develop queries with
outer joins in order to show locations and test conditions for which
results have not been entered, as well as those for which results have
been entered. This is easy to do.

You first create a union query of the location condition and test
condition table primary keys (look up UNION in Help).

You then create a new query that outer joins that union query to the
test results table. You create an outer join in query design by double
clicking on the line connecting the union query to the test results
table. This pops open the "Join Properties box", where you select the
"Include ALL records from (union query), and only those records from
(test results table) where the joined fields are equal". The resulting
query will show all test conditions and location conditions with their
results, with nulls for results in the locations for which results have
not been entered.

I think you'll find this structure to be more flexible and useful than
the one you currently have.