hi Jeff,

Thanks for your detailed response! Obviously I was more out of my depth than
I thought. I think I have a lot of reading to do tonight and tomorrow to
digest yours and Ken's responses, and I will see if your instructions help me
solve my problem, or if I have to come back with more clarifications.

cheers,
alia

"Jeff Boyce" wrote:

I'm not sure I'm correctly visualizing your data structure yet...

To get the best use of Access' relationally-oriented features/functions, you
need to 'feed' it well-normalized data.

When you described your database, the example you gave showed a LocationID
in the same record as a SampleNumber, along with several other fields that
appear to be information about the location. If this is an accurate
paraphrase of your data structure, you may need to brush up on
"normalization". It may be because I don't understand your situation, but
my first impression is that a "location" table would only have information
about locations, not about samples.

Let me try something here -- the following is just a wild guess, so feel
free to correct it ...

You have locations. Many of them.

Each of your locations can have multiple samples. ... or just one, or none!

Each of your samples is assayed (but only once). (... but I can't tell
what's involved in an assay -- a single thing being measured or multiple
things being measured?)

If these are accurate statements, then a well-normalized table structure in
Access might look something like (untested):

tblLocation
LocationID
LocationName
LocationCoordinates
... any other fields that are ONLY about Location
(... and I don't understand how "regions" relate to "locations")

tblSample
SampleID
SampleDate
Sampler
... any other fields that are ONLY about samples

trelLocationSample
LocationSampleID
LocationID (a foreign key, pointing back to the location table)
SampleID (a foreign key, pointing back to the sample table)

(... and because I don't know enough about assays, I don't know how they
relate to the above)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--