Many to many tables

[Paul]

I want to make a database for lab results. In order to
have the input forms resemble the paper copies I have had
to make separate tables for various results (based on
frequency of sampling).

If I make a single table with all the analyses listed,
there would be lots of empty cells since not all would be
done for certain date and location(not good design).

If I make a single table with a field for analysis (and
another for the value) then I haven't found a way to make
a form to display the groups of analyses together. This
also requires a single form to create multiple records,
which doesn't appear possible.

The separate tables solves those problem, but now I have
problems to query since there is a many to many
relationship between all the fields (sampling location,
date, analysis).

I think having the analysis name as a field is the best
design if there was a way to make the forms I need,
otherwise maybe a query of a query in the multi table
design. I don't know how to do either at this point,
where do I go?

 

[Jeff Boyce]

Paul

It sounds like you have designed your tables to reflect your paper forms.
Access is a relational database -- you won't be able to take full advantage
of its features with a table design like that.

Read up on "normalization" if you want to use more of what Access can do.