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
|
|||
|
|||
primary keys and relationships between tables
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. -- Thank you, Sarah |
#2
|
|||
|
|||
primary keys and relationships between tables
On Thu, 5 Mar 2009 16:27:01 -0800, gurl_bytes
wrote: 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/acc...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 -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|