relationships

[pajo]

This is probably easy, but I just started using Access last week. I have read a couple of books but am still stumped on how to set up this database.

I have three tables in a database, plots;subplots;species.

The plot table contains general information about each large plot such as plot ID field, geographic location, date of survey etc.
The subplot table has a plot ID field and depending on the plot, 1-5 subplots. Each subplot may be of a different type of habitat e.g. wetland, river, marsh.
The species table has a plot ID field, subplot field and a species observed for each subplot. Each subplot may have 1 or more species observations.On this table I can record what species were found on each subplot.

I have the species form set up so that each observation of a species is a new record and just manually type in the plot ID number. This works OK but I want to know how to relate the tables to query.

For example I want to be able to query all plots with "rivers" (from the subplot table) that have species "duck" from the species table?

I hope I'm on the right track. Any advice is appreciated.

Ed

 

[boblarson]

Take out the plot ID field from the species table if you are tracking species by subplot, because you can find out which plot a species belongs to by tying the species, subplot, and plot table back together via a query.

In a new query (using the QBE grid), add the tables in and, if you haven't set the relationships, drag the PlotID from the plot table to the PlotID in the subplot table and then drag the SubPlotID from the subplot table to the SubPlotID in the species table. Then, add the fields you want from each table and then run the query (the Exclamation Point Button) to view it.

Next, you can create a form or report based on that query.

 

[pajo]

Hi Bob

Thanks for the advice!

Ed