Design of tables

[Guest]

Hi need to set up a database to record the name and location of plants on a
large piece of land.

I think I need a table for plant information ( Genus (cultivar date
introduced, native to site, etc.)
I also need to record the location. There are 21 locations and a plant type
can be in more than one location. I think a seperate table is needed for
location linked many to many with the other. I then need forms to enter data
and and also for people to query it. For instance to find all locations of a
plant or all native plants.
I am not sure how to construct the form to be able to slect the locations
other than having 21 fields with a checkbox to select.
there must be a more elegant way to do this. Can anyone help pleaSE?
Thanks

 

[Guest]

Make a table for plant information, along with a primary key. Make a table
for your discrete locations, also with a primary key. Now, make a third
table, with its own discrete primary key, that will include the primary key
names from the other two tables. Go into relationships and relate the plant
info and location info tables to your third table, by way of those primary
key names.

Then you make a form, based on that third table, that has a combo box for
choosing the plant, and another for choosing the location, each time you wish
to record a new plant and location. Then make some reports that sort by
location or sort by plant, or filter information by whatever criteria you
want.

The nice thing about this is that you can add plants and/or locations
whenever you want, and just start adding information about them. Also, any
specific information about the plants or locations can be put into those
specific tables, and reports generated just on that information.

 

[Guest]

Looks like you are on the right track but about to spin out into the woods
with the 21 check box idea. ;-)

Bridging or linking table for the Many-to-Many relationship should have at
least three fields: its own primary key (autonumber), a foreign key to the
Plants table, and a foreign key to the Locations table.

Create a form based on this bridging table. Next change the text fields for
the FK fields to combo boxes. (In design mode right click on the text fields
and Change To, Combo Box). Make the row source of the combo box the Plants
or Location table as appropriate. Make the first column the primary key and
add in any other needed information. If you don't want to see the PK in the
combo box, make the column size 0.

Now with this form open you can first pick a plant and then a location. Move
to the next record and do again. I like using the continuous form view for
this.

How to query it is another question for another time.