If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Access Conditional Relationship
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] |
#3
|
|||
|
|||
Access Conditional Relationship
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 |
Thread Tools | |
Display Modes | |
|
|