Importing/Appending New Data to a Table

[Armand]

A database I've inherited takes the form of the following fields and
records:

[Well] [Param] [SampDate] [Num]
1 A Jan 1
1 B Jan 2
1 C Jan 1
2 A Feb 3
2 B Feb 2
2 C Feb 2
1 A Mar 5
1 B Mar 3
1 C Mar 2
2 A Mar 3
2 B Mar 1
2 C Mar 4
... etc ...

There are 34 [Well]s (and more to be added) and each well has about 20
[Param]s that are measured at various times during the year.

The data I'm trying to append to this table comes in a different form:
[Well] [Param] [SampDate] [Num]
1(1) Aa Oct 1
1(1) Bb Oct 2
1(1) Cc Oct 1

The [Well] "1" is the same as [Well] "1(1)" and [Param] A is the same
as "Aa".

I'm trying to append the new data to the existing, but when I run the
other queries and reports, the data for [Param] "Aa" isn't reported in
the same record as the [Param] "A".

Am I best to do a Find/Replace in the new data so they match the
existing [Well] and [Param] or is there an easier way within the
database?

Thanks,
Armand

 

[Nick Coe \(UK\)]

Either append (or update) the first char of the imported
Param field using Left([Param],1) in the appropriate query.

 

[Armand]

Either append (or update) the first char of the imported
Param field using Left([Param],1) in the appropriate query.

Thanks, Nick. I guess in my explanation, I simplified things a little
too much - the [Param] are not quite as simple in my example.

For some of the [Param]s I could do as you suggested
(i.e., "Ammonia, NH3" = "Ammonia")
but for others, it wouldn't work
(i.e., "Alkalinity" --> "Total Alkalinity (as CaCO3)")

Find and Replace??

Armand

 

[Nick Coe \(UK\)]

Armand,

Yup, looks like Find and Replace... :-(

Because there's no consistant seperator (i.e. comma, pipe,
tab etc) or start position, you can't easily parse out the
words you want.

You could create a dictionary table with the words to search
for and mess with InStr(), Left(), Mid() and so on but that
could be a big job and would only be worth doing if you have
to repeatedly import such data. You'd also need to address
which occurance of a word you wanted if it should appear in
the string more than once

Sorry I can't be more help.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

In Armand typed:

Either append (or update) the first char of the imported
Param field using Left([Param],1) in the appropriate
query.

Thanks, Nick. I guess in my explanation, I simplified
things a little
too much - the [Param] are not quite as simple in my
example.

For some of the [Param]s I could do as you suggested
(i.e., "Ammonia, NH3" = "Ammonia")
but for others, it wouldn't work
(i.e., "Alkalinity" --> "Total Alkalinity (as CaCO3)")

Find and Replace??

Armand