Access Conditional Relationship

I am working on a fossil database in Access 97 and an stumped on how to build
a conditional relationship. I have 3 main tables (age, specimen, and fossil)
that are pretty straightforward. The problem I am encountering is that I
also have a group of tables for each of the animal phylums. These tables
have 100 to 700 records and each record has about 30 fields containing
phylum, subphylum, class, subclass, etc. Each fossil is related to one
phylum and therefore one table. In each fossil record, I have 2 fields set
up to do the link - a plylum field and a record number field. The phylum
value is the table name that contains the correct number and the record
number is the record number in the selected phylum that contains the correct
information. To get it to work properly in forms and reports, I thought
about using the CASE statement in VB to build the relationship as it read
each record, but I was unable to figure out how to make it work. Any advise
on how to make this work would be greatly appreciated or if I should set
things up differently. I am hoping to keep the phylums in separate tables,
but if the only way to make this work is to combine all phylums into one big
table, I can do that. Thanks much. joe

On Thu, 2 Oct 2008 12:38:00 -0700, Joe Obertin
wrote:

I am working on a fossil database in Access 97 and an stumped on how to build
a conditional relationship. I have 3 main tables (age, specimen, and fossil)
that are pretty straightforward. The problem I am encountering is that I
also have a group of tables for each of the animal phylums. These tables
have 100 to 700 records and each record has about 30 fields containing
phylum, subphylum, class, subclass, etc. Each fossil is related to one
phylum and therefore one table. In each fossil record, I have 2 fields set
up to do the link - a plylum field and a record number field. The phylum
value is the table name that contains the correct number and the record
number is the record number in the selected phylum that contains the correct
information. To get it to work properly in forms and reports, I thought
about using the CASE statement in VB to build the relationship as it read
each record, but I was unable to figure out how to make it work. Any advise
on how to make this work would be greatly appreciated or if I should set
things up differently. I am hoping to keep the phylums in separate tables,
but if the only way to make this work is to combine all phylums into one big
table, I can do that. Thanks much. joe

Storing data in tablenames (phyla) is *simply wrong design*. You cannot use a
criterion or a value in a table to link to the correct phylum table; you would
have to build the entire SQL string in code. You'ld do much better using one
big Taxon table with a field (indexed of course!) for Phylum. I don't know
what your "record number" field might be - Access doesn't use record numbers;
is it perhaps an autonumber value?

What disadvantage do you see in having all your taxonomic data in one table?
--

John W. Vinson [MVP]

Joe:

Combining all the phylum records into a single table is really the only way
to go which makes any sense. At present you are encoding data as table names
whereas it’s a fundamental principle of the database relational model that
data is stored as values at column positions in rows in tables and in no
other way.

However, that's not the end of the story as even if you combined the current
phyla tables into one the table it would contain a vast amount of redundancy,
e.g. you'd be told many times that a subphylum was within a particular
phylum, and similarly that a class was within a particular subphylum and so
on down the line.

The process of eliminating redundancy in a table is known as normalization
and is accomplished by decomposing the table into related tables. So you'd
have a table Phyla with a column Phylum as its primary key and one row per
phylum. You'd then have table Subphyla related to this. This table would
have a column Subphylum as its primary key and a column Phylum as a foreign
key referencing the primary key of Phyla, and would contain one row per
subphylum. next would be a column Classes and so on until you reach the
bottom of the taxonomic hierarchy (species? subspecies?).

The fossils table need only be related to the lowest level table of the
taxonomic hierarchy e.g. species or subspecies (I'm afraid my knowledge of
taxonomy is only sketchy), so you'd just need a foreign key column in the
Fossils table which references this lowest level table. You then know all of
the upper levels for that fossil by virtue of the series of many-to-one
relationships right up to the phylum.

In each table be sure to index the foreign key column non-uniquely
(duplicates allowed).

There are a number of ways you can design a form for inputting fossil
records. One would be to base the form on a query which joins the Fossils
table to the other tables by a series of joins. Text boxes bound to the
relevant columns from each table would then be automatically filled when you
select a value for the foreign key column in the Fossils table. So if this
is Species say you'd bind a combo box to this column and set its RowSource
property to:

SELECT Species FROM Species ORDER BY Species;

You can then either scroll down the list and select a value or by tying in
the value the combo box will go to the first match as each character is typed.

You might think that merging and then decomposing the current phyla tables
is going to be an unrealistically large task, but its quite straightforward
in fact. The first thing is to merge all the rows from you current table
into one table by a series of Append queries into a pre-designed AllPyla
table. This table

You can then design the Phyla, Subphyla, Classes etc tables, making sure you
set the primary key in each case and append rows from the AllPhyla table, so
firstly you'd populate the Phyla table with:

INSERT INTO Phyla (Phylum)
SELECT DISTINCT Phylum
FROM AllPhyla
ORDER BY Phylum;

Then populate the Subphyla table with:

INSERT INTO Subphyla (Subphylum, Phylum)
SELECT DISTINCT Subphylum, Phylum
FROM AllPhyla
ORDER BY Subphylum;

Then for Classes:

INSERT INTO Classes (Class, Subphylum)
SELECT DISTINCT Class, Subphylum
FROM AllPhyla
ORDER BY Class;

and so on down the line.

Finally you'd add the Species foreign key column (or whatever is the lowest
level of the hierarchy which you are referencing) to the Fossils table.
You'd then populate this by a series of update queries which join the Fossils
table to each of the current phyla tables in turn, using the join fields as
you describe, updating the Species column (or whatever) to the value of the
Species column from the current phylum table in question.

Ken Sheridan
Stafford, England

"Joe Obertin" wrote:

I am working on a fossil database in Access 97 and an stumped on how to build
a conditional relationship. I have 3 main tables (age, specimen, and fossil)
that are pretty straightforward. The problem I am encountering is that I
also have a group of tables for each of the animal phylums. These tables
have 100 to 700 records and each record has about 30 fields containing
phylum, subphylum, class, subclass, etc. Each fossil is related to one
phylum and therefore one table. In each fossil record, I have 2 fields set
up to do the link - a plylum field and a record number field. The phylum
value is the table name that contains the correct number and the record
number is the record number in the selected phylum that contains the correct
information. To get it to work properly in forms and reports, I thought
about using the CASE statement in VB to build the relationship as it read
each record, but I was unable to figure out how to make it work. Any advise
on how to make this work would be greatly appreciated or if I should set
things up differently. I am hoping to keep the phylums in separate tables,
but if the only way to make this work is to combine all phylums into one big
table, I can do that. Thanks much. joe