primary keys and relationships between tables

[gurl_bytes]

HI, Im a geo-spatial type and Im trying to make a simple access database for
a tree planting firm who is currently using an excel spreadsheet with
hundreds of tabs as their database....So, Ive been asked to fix this up and
convert the excel data into the new access database. I have made a database
with 6 tables
1) Project_ID
2) Planting_Phase (includes, GPS lat/long, tree species,etc. fields, mostly
as long integer)
3) Maintenance_Phase1
4)Maintenance_Phase2
5)Maintenance_Phase3
6)Maintenance_Phase4

in each table i have put the unique project ID number as the 'primary key'
but am not sure if this is correct. then I have created one to one
relationships between each table linking the Primary keys together. Please
don't laugh! I have never made one of these before so if you know what to do
please hit me asap. Thank you so much.

 

[John W. Vinson]

HI, Im a geo-spatial type and Im trying to make a simple access database for
a tree planting firm who is currently using an excel spreadsheet with
hundreds of tabs as their database....So, Ive been asked to fix this up and
convert the excel data into the new access database. I have made a database
with 6 tables
1) Project_ID
2) Planting_Phase (includes, GPS lat/long, tree species,etc. fields, mostly
as long integer)
3) Maintenance_Phase1
4)Maintenance_Phase2
5)Maintenance_Phase3
6)Maintenance_Phase4

in each table i have put the unique project ID number as the 'primary key'
but am not sure if this is correct. then I have created one to one
relationships between each table linking the Primary keys together. Please
don't laugh! I have never made one of these before so if you know what to do
please hit me asap. Thank you so much.

Linking primary keys to primary keys is, as you've apparently concluded, not a
good idea. But we can help you normalize your design, I hope!

The key concept is that each table should represent a particular type of
Entity. A Project is certainly one type of entity, so you should have a table
of Projects; a tree species is a different type of entity, so you should have
a table of Species, e.g.

SpeciesID <autonumber primary key>
Genus <"Acer" say>
Species <"rubrum">
Variety <"Crimson Cloud">
CommonName <"Red Maple">
<etc>

Presumably a Project will have many Plantings so you would have a one to many
relationship from the Project table to a Plantings table with a specific
location (do you gps tree plantings down to the individual seedling!?) and
SpeciesID, and perhaps fields for planting date, comments, etc.

I have no idea what's in your maintenance tables but my guess is that you need
ONE maintenance table not four, with a field for Phase; there may well be
other tables involved as well.

You might want to look at some of the resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials