Linking tables from existing data

[JB Akron]

Access 2003- I have two tables with large amounts of data. One table
contains species data so there are multiple records for one site. The other
table is basically a summary habitat score for the site . So table 1 is
considerably larger than table 2. Here is the problem that I am facing.
Autonumbering the primary key for the species data will create a unique
record for each but autonumbering table 2 would cause a mismatch and data
would not be queried properly. Basically the site information is stored
separately in 4-5 columns (i.e. basin, river code, river name, river mile).
Both tables have the same column information but I can't select 4 primary
keys? How should I best link the information between to the two tables
without going through the lengthy process of creating a master site numbering
list (ie. 001-012-rivername-rivermile)

New to access- any insight will help- been through the tutorials, thanks

 

[John W. Vinson]

On Sat, 29 Nov 2008 12:03:01 -0800, JB Akron <JB

Access 2003- I have two tables with large amounts of data. One table
contains species data so there are multiple records for one site. The other
table is basically a summary habitat score for the site . So table 1 is
considerably larger than table 2. Here is the problem that I am facing.
Autonumbering the primary key for the species data will create a unique
record for each but autonumbering table 2 would cause a mismatch and data
would not be queried properly. Basically the site information is stored
separately in 4-5 columns (i.e. basin, river code, river name, river mile).
Both tables have the same column information but I can't select 4 primary
keys? How should I best link the information between to the two tables
without going through the lengthy process of creating a master site numbering
list (ie. 001-012-rivername-rivermile)

New to access- any insight will help- been through the tutorials, thanks

STOP!!!!

You're misunderstanding how tables and primary keys work.

Each table should represent one kind of entity (a real-life person, thing, or
event). A species is one type of entity; a site is a very different type of
entity; the observation of a species at a site is yet another type of entity.

The Primary Key of a table is a field - *or a combination of up to ten fields*
- which (jointly, for the multiple fields) uniquely identify one individual
entity of that type.

Each table should contain ONLY fields relevant to that entity - and should not
contain fields relevant to other entities. A species of snail does not have a
river name, or a basin... a river does not have a genus or a species!

I'd see *four* tables:

Rivers
RiverCode <Primary Key>
RiverName
Basin <perhaps you want a table of basins too?)
<other information about the site itself>

Species
SpeciesID (autonumber or better a published unique species code)
Species
Genus
CommonName
<other info about the species>

Observations
RiverCode <which river... you DON'T need the rivername here, look it up!>
RiverMile <where on the river>
SpeciesID <which species was observed here>
<information about this observation, e.g. date observed, by whom, number,
etc.>

The Primary Key of Observations could consist of RiverCode, RiverMile,
SpeciesID, and perhaps ObservationDate; you can create a multifield primary
key by ctrl-clicking the fields and then clicking the Key icon.