Need Help with Desing - Lists

[Blair]

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]

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