alia:

As there is only one sample per location this means that the relationship
type between Locations and samples is one-to-one. This is achieved by
indexing the LocationID foreign key column in the Samples table uniquely.
One-to-one relationship types are not very commonly used as the data could be
combined into one table, but in this case it's appropriate to have separate
tables as there will be locations without samples (but not vice versa
obviously). It's important that the 'direction' of the relationship is from
Locations to Samples therefore, so when creating it in the relationships
window drag from the primary key of Locations to the foreign key of Samples.

Whether you can append all the result categories in one go really depends how
many there are. If the number is reasonable you can first create a UNION ALL
query which tacks the results from the different columns together in one
result table, e.g.

SELECT [sample number],
"Some_column_name" AS Result Category,
Some_column_name AS Result
FROM [TheLinkedCSVFile]
WHERE Column_name IS NOT NULL
UNION ALL
SELECT [sample number],
"Some_other_column_name",
Some_other_olumn_name
FROM [TheLinkedCSVFile]
WHERE Some_other_olumn_name IS NOT NULL
UNION ALL
SELECT [sample number],
"Yet_another_column_name",
Yet_another_column_name
FROM [TheLinkedCSVFile]
WHERE Yet_another_olumn_name IS NOT NULL;

For each result category just add another SELECT etc to the query, tacking
them together with UNION ALL in each case. If you want to restrict it to one
sample you can add a parameter to each WHERE clause to prompt you to enter
the sample number at runtime:

SELECT [sample number],
"Some_column_name" AS Result Category,
Some_column_name AS Result
FROM [TheLinkedCSVFile]
WHERE ([sample number] = [Enter sample number:]
OR [Enter sample number:] IS NULL)
AND Column_name IS NOT NULL
UNION ALL
SELECT [sample number],
"Some_other_column_name",
Some_other_column_name
FROM [TheLinkedCSVFile]
WHERE ([sample number] = [Enter sample number:]
OR [Enter sample number:] IS NULL)
AND Some_other_olumn_name IS NOT NULL
UNION ALL
SELECT [sample number],
"Yet_another_column_name",
Yet_another_column_name
FROM [TheLinkedCSVFile]
WHERE ([sample number] = [Enter sample number:]
OR [Enter sample number:] IS NULL)
AND Yet_another_olumn_name IS NOT NULL;

Testing the parameter for OR IS NULL means that its optional, so leaving it
blank at the prompt would append rows for all samples.

You can then base the append query on the above query, which I'll call
qryAllResults for this example:

INSERT INTO AssayResults
(SampleNumber, ResultCategory, Result)
SELECT [sample number], ResultCategory, Result
FROM qryAllResults;

If the number of result categories is very large you could still do it in one
operation by automating the execution of separate append queries, which you'd
build in code each time rather than creating and saving individually. This
would be done by creating a little dialogue form in which you'd enter the
sample number (this could be optional, so that leaving the number blank would
append rows for all samples), and then click a button whose code would loop
through the ResultCategories table and execute an append query for each
category. Your are probably recoiling in horror at the thought of having to
dabble in the black art of VBA coding, but don't worry, it's not as scary as
it sounds and we can help you with it. But see if you can do it with a UNION
ALL query first as that's a more appropriate solution if possible.

Ken Sheridan
Stafford, England

alia wrote:
hello Ken,

I did some reading through the Wiki entry on normalization and it helped
clarify things for me, so I've taken a stab at reorganizing the db I was
working on based on that and your comments.

I have separate tables for Location, Sample, and AssayResult. The 'region'
column refers to the named area a location is found in, and I think isn't
really necessary for the Access side of things. (I'm not sure it's even
relevant in MapInfo, because the points all have UTM coordinates that could
be pulled out with a query that would pick out point locations inside
specified polygon regions. So I may just remove that information all
together.)

The 'append' process was the part I was especially wondering about, as far
as getting the data in to the db, so thank you for that as well.

[snip]
Assuming a location might have more than one sample taken a table of the
structure you cite:
[quoted text clipped - 6 lines]
of these attributes being entered in separate rows for the same location.
Separate Locations, Regions and Samples tables are needed, e.g.

I checked into this and I've been told that there will only ever be one
sample per location, because if the geos are going to take a new rock sample
they will take a new GPS reading for the new location. Does that change any
of your suggestions?

[snip]
Turning to the assay results a suitable table for this would be:

[quoted text clipped - 34 lines]
FROM [TheLinkedCSVFile]
WHERE Pt_ppm IS NOT NULL;

If I wanted to bring in all the result columns for a particular assay, would
I have to set an individual append query like the above up for each column,
like the following:

INSERT INTO AssayResults
(SampleNumber, ResultCategory, Result)
SELECT [sample number], "Column_name", Column_name
FROM [TheLinkedCSVFile]
WHERE Column_name IS NOT NULL;

or is there a way to bring in all the assay data for each sample?

Whenever you receive a .csv file with new assay results it simply a case of
linking to the .csv file and executing the set of append queries, which can
[quoted text clipped - 10 lines]
create a query to return the data in a format compatible with MapInfo's
requirements.

So it would be a matter of creating a query that would in effect combine all
the tables I broke out into separate entities into one query result, and then
exporting that result into a .csv (MapInfo has no trouble with .csv or .xls
or any of that sort of data, which is nice).

I have a couple of screenshots of how I've set things up so far; if they
would be more helpful in seeing what I'm working with, I can post the links.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201005/1