Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / July 2005

Tip: Looking for answers? Try searching our database.

Need Help with Desing - Lists

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Blair - 05 Jul 2005 17:44 GMT
I have created a database that holds information about different land
properties. It holds such info as location, owner etc.  Now I need to include
information such as which plant species are found on each site and I'm not
sure how to incorporate this into the table.  This info is great in excel
becuase I can just list species down the side and then for each site say yes
or no if it  is there.  But how can this info be put into a database?  I need
to be able to query on each species.  For each site I need to specify whcih
species were found.

I would link to an excel sheet but with many properties and this info being
collected each year, that will lead to far too many tables to link.  Any
suggestions would be greatly appreciated.
tina - 05 Jul 2005 18:06 GMT
you didn't describe the table(s) in your db, but i'm assuming you have a
table that lists all your land properties, with a primary key field to
uniquely identify each property.

create a separate table to list all plant species (if you don't have one
already). again, make sure the table includes a primary key field.

these two tables have a many-to-many relationship:  one property may have
many plant species on it, and one plant species may grow on many properties.
in a relational database, you resolve a many-to-many relationship with a
"linking" table that has a many-to-one relationship with each of the parent
tables.

so create a third table as your linking table. each record will be linked to
a specific property AND a specific plant species. tables example:

tblLandProperties
PropertyID (primary key field)
(other fields that describe the property)

tblPlantSpecies
SpeciesID (pk field)
SpeciesName
(other fields that describe the species)

tblPropertyPlants (this is the linking table)
PropertyID (foreign key from tblLandProperties)
SpeciesID (fk from tblPlantSpecies)
(other fields that describe the plant on the property)
(note:  you may use the two foreign key fields to create a combination
primary key for this table, or you can add a separate field - probably data
type of Autonumber - to serve as the table's primary key field.)

so if you have land properties A, B, and C, and plant species Tree, Flower,
Weed, your linking table's data might look like this

PropertyID          SpeciesID
A                              Tree
A                              Weed
B                              Weed
C                              Flower
C                              Weed
C                              Tree

you can easily query by plant species OR by land property.

hth

> I have created a database that holds information about different land
> properties. It holds such info as location, owner etc.  Now I need to include
[quoted text clipped - 8 lines]
> collected each year, that will lead to far too many tables to link.  Any
> suggestions would be greatly appreciated.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.