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.