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

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



"alia" wrote in message
...
hi Jeff,

Thanks for answering. Sorry that wasn't clear. I also got some more
information from the geologist who is going to be using this db, so here
it
is.

The lab assay .csv will have a column for the sample number and the assay
results:

sample number | Au_ppm | result2 | result3 | ...etc.

I want to bring in the relevant result (in this case, gold in parts per
million) into my database, which contains columns like:

location ID | northing | easting | region name | sample number |

So I want to link up the two SampleNumber fields and bring in the relevant
Au_ppm information (and not every location ID will have a sample taken).
The
goal is to be able to export this information into another .csv to display
the information in MapInfo.

I tried this with some made-up data and the problem that I had was that
when
I tried to append the data to the table, I ended up with duplicates in the
SampleNumber field. So I'm not sure how to fix that. And, more generally,
I'm
not sure what the most appropriate way to do this will be - link the
table,
import the data, or append a copy. We're going to have a lot of these
assay
results so there will be a lot of data to handle.

Hope this helps.

thanks,
alia

"Jeff Boyce" wrote:

To connect a sample to a location, you need to have a way to, well,
connect
the sample to the location...

Does the .csv sample data include a locationID, or LocationName, or some
such?

Do you have a list of all possible Locations? ?with IDs?

Can you have more than one assay result per sample? How do you connect
an
assay result to a sample ... do you have a SampleID (i.e., a unique
identifier for each sample)?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.