organization and keys

i have data concerning tree demographics: census date, stage, size, species,
tag id.

currently, the table has unique keys for each tree for each year. there are
1700 or so trees and because of multiple censuses, there are over 5100
records, each with a unique key. i want to be able to use the tree's tag id
(how it is identified in the field) as its key, so to speak. i understand
keys are supposed to be unique and this will run counter to that principle
because the tag id/key would appear each year the tree shows up in a census,
i.e. 1X-9999(tag id) will have data for 1998, 2002, 2006, so there will be a
record for each year the tree is present. is this possible?

also, to make this more complicated, when trees are determined to change
from juvenile to adult, their tag id changes. would it be possible to somehow
transfer records from one key to another, such as when a tree moves from a
juvenile tag id to a new tag id?

On Sat, 27 Dec 2008 13:45:01 -0800, inACCESSable
wrote:

You need multiple tables. The first one will keep invariant
information about each tree:
tblTrees
treeID autonumber PK
treeSpeciesID long int required

A lookup table for species. This will later be used for Species
dropdown:
tblSpecies
treeSpeciesID autonumber PK
treeSpecies text(255) required, unique index

To collect data for a census:
tblCensusData
treeID
censusDate
Stage (?Is this juvenile/adult?)
Size
Tag (?)

Tag is a discussion item. Is a tree (re-)tagged when a census is
taken? In some situations I could be persuaded to allow a JuvenileTag
and AdultTag field. It really depends on the finer points of the
requirements.

If tags are applied independent from a census, I would go for:
tblTags
Tag, PK
TreeID long int, required
TagStage (Juvenile/Adult), required

Don't forget to use the Relationships window and draw all relations
between the tables, and enforce them.

-Tom.
Microsoft Access MVP


i have data concerning tree demographics: census date, stage, size, species,
tag id.

currently, the table has unique keys for each tree for each year. there are
1700 or so trees and because of multiple censuses, there are over 5100
records, each with a unique key. i want to be able to use the tree's tag id
(how it is identified in the field) as its key, so to speak. i understand
keys are supposed to be unique and this will run counter to that principle
because the tag id/key would appear each year the tree shows up in a census,
i.e. 1X-9999(tag id) will have data for 1998, 2002, 2006, so there will be a
record for each year the tree is present. is this possible?

also, to make this more complicated, when trees are determined to change
from juvenile to adult, their tag id changes. would it be possible to somehow
transfer records from one key to another, such as when a tree moves from a
juvenile tag id to a new tag id?

On Sat, 27 Dec 2008 13:45:01 -0800, inACCESSable
wrote:

i have data concerning tree demographics: census date, stage, size, species,
tag id.

currently, the table has unique keys for each tree for each year. there are
1700 or so trees and because of multiple censuses, there are over 5100
records, each with a unique key. i want to be able to use the tree's tag id
(how it is identified in the field) as its key, so to speak. i understand
keys are supposed to be unique and this will run counter to that principle
because the tag id/key would appear each year the tree shows up in a census,
i.e. 1X-9999(tag id) will have data for 1998, 2002, 2006, so there will be a
record for each year the tree is present. is this possible?

also, to make this more complicated, when trees are determined to change
from juvenile to adult, their tag id changes. would it be possible to somehow
transfer records from one key to another, such as when a tree moves from a
juvenile tag id to a new tag id?

A table's Primary Key can be one field - or it can even be ten fields if you
wish!

I'd suggest that the Primary Key of your table consist of two fields: the
TagID (Text I presume) and CensusYear. That way you could have records like

2001; 1X
2002; 1X
2001; 1265
2002; 1265
2003; 1265

Either field can contain multiple records with the same value, but the
combination will be enforced to be unique.

You may want to consider whether the tree in fact changes its essential
identity on becoming an adult: perhaps you need a unique key for a tree, and a
separate field for the current tag. Or, you could have an OldTag field; when
an adult tag is assigned you could copy the juvenile tag into OldTag and
replace the Tag value with the new tag. A separate Tags table will be better
if there will ever be a *third* tag assigned to the same tree.
--

John W. Vinson [MVP]

On Sat, 27 Dec 2008 13:45:01 -0800, inACCESSable wrote:

i have data concerning tree demographics: census date, stage, size, species,
tag id.

currently, the table has unique keys for each tree for each year. there are
1700 or so trees and because of multiple censuses, there are over 5100
records, each with a unique key. i want to be able to use the tree's tag id
(how it is identified in the field) as its key, so to speak. i understand
keys are supposed to be unique and this will run counter to that principle
because the tag id/key would appear each year the tree shows up in a census,
i.e. 1X-9999(tag id) will have data for 1998, 2002, 2006, so there will be a
record for each year the tree is present. is this possible?

also, to make this more complicated, when trees are determined to change
from juvenile to adult, their tag id changes. would it be possible to somehow
transfer records from one key to another, such as when a tree moves from a
juvenile tag id to a new tag id?

Maybe this subtype structure would work.

CREATE TABLE Trees (
TreeID IDENTITY(1,1) NOT NULL,
Stage VARCHAR(10) NOT NULL,
CHECK (Stage IN ('juvenile','adult')),
Species VARCHAR (20) NOT NULL,
PRIMARY KEY (TreeID,Stage));


CREATE TABLE TreesJuvenile (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT 'juvenile' NOT NULL,
CHECK (Stage ='juvenile'),
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID));


CREATE TABLE TreesAdult (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT 'adult' NOT NULL,
CHECK (Stage ='adult'),
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID));

Maybe implement this as a main form based on Trees with
two tabs, one tab for a continuous subform based on TreesJuvenile
and the other tab for a continuous subform based on TreesAdult.
This could be roughly built using the wizards in about 10 minutes.

This is just an idea for consideration. I do not know that it is the correct model.

On Sat, 27 Dec 2008 19:35:32 -0600, Michael Gramelspacher wrote:

On Sat, 27 Dec 2008 13:45:01 -0800, inACCESSable wrote:

i have data concerning tree demographics: census date, stage, size, species,
tag id.

currently, the table has unique keys for each tree for each year. there are
1700 or so trees and because of multiple censuses, there are over 5100
records, each with a unique key. i want to be able to use the tree's tag id
(how it is identified in the field) as its key, so to speak. i understand
keys are supposed to be unique and this will run counter to that principle
because the tag id/key would appear each year the tree shows up in a census,
i.e. 1X-9999(tag id) will have data for 1998, 2002, 2006, so there will be a
record for each year the tree is present. is this possible?

also, to make this more complicated, when trees are determined to change
from juvenile to adult, their tag id changes. would it be possible to somehow
transfer records from one key to another, such as when a tree moves from a
juvenile tag id to a new tag id?

Maybe this subtype structure would work.

CREATE TABLE Trees (
TreeID IDENTITY(1,1) NOT NULL,
Stage VARCHAR(10) NOT NULL,
CHECK (Stage IN ('juvenile','adult')),
Species VARCHAR (20) NOT NULL,
PRIMARY KEY (TreeID,Stage));


CREATE TABLE TreesJuvenile (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT 'juvenile' NOT NULL,
CHECK (Stage ='juvenile'),
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID));


CREATE TABLE TreesAdult (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT 'adult' NOT NULL,
CHECK (Stage ='adult'),
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID));

Maybe implement this as a main form based on Trees with
two tabs, one tab for a continuous subform based on TreesJuvenile
and the other tab for a continuous subform based on TreesAdult.
This could be roughly built using the wizards in about 10 minutes.

This is just an idea for consideration. I do not know that it is the correct model.

Sorry, the above model is wrong. This seems to work though.

CREATE TABLE Trees (
TreeID LONG NOT NULL,
Stage VARCHAR(10) NOT NULL,
CHECK (Stage IN ('juvenile','adult')), -- use validation rule for this
Species VARCHAR (20) NOT NULL,
PRIMARY KEY (TreeID,Stage));


CREATE TABLE TreesJuvenile (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT juvenile NOT NULL,
CHECK (Stage ='juvenile'), -- use validation rule for this
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID, Stage,TagNumber,CensusDate));


CREATE TABLE TreesAdult (
TreeID LONG NOT NULL,
Stage VARCHAR(10) DEFAULT adult NOT NULL,
CHECK (Stage ='adult'), -- use validation rule for this
TagNumber LONG NOT NULL,
CensusDate DateTIME NOT NULL,
TreeSize DECIMAL (6,2) NOT NULL,
FOREIGN KEY (TreeID,Stage)
REFERENCES Trees (TreeID,Stage),
UNIQUE (TagNumber,CensusDate),
PRIMARY KEY (TreeID, Stage,TagNumber, CensusDate));