I'm trying to automate an import mechanism in an Access 2003 database that will contain analytical results for specific chemicals. The thing is, not all of the labs report results using the same names for each chemical parameter. For example, one lab may list MTBE as "MTBE" and another may list it as "methyl tert butyl ether"...I need to figure out a way for Access to determine that they are the same chemical based on character matching or a similar method. Access will have to compare the chemical name in the import data with records in a pre-made table containing known names for each chemical, a unique ID number for each chemical and the "official" name to be used in database reports, forms, etc. This all needs to happen in a sub/function procedure (or series of sub/function procedures) that will be called in the middle of a larger import module.

I've tried using an ADO sub procedure that opens recordsets and then applies SQL WHERE and LIKE statements to no avail. The database will be completely self contained, so I have no preference between DAO or ADO methods. The user base is such that it's not possible for me to tell them to open the import file and manually change values in the parameter field. I think I can handle the rest of the import module...but this text matching problem is beyond me. Thanks in advance for any help!

Where is this dirty data coming from? Is it in an Access table, or a text file, or some other location? That would be helpful for any code.

What I would suggest is the you create the official chemical name table, and then create a synonym dictionary table. They would have this structure:

(Official table)
tblChemical
ChemicalID - PK
ChecmicalName - Text
otherdata?

(Synonym table)
tblChemSyn
ChemSynID - PK
ChemicalID - FK to tblChemical
Synonym - text

Then when you are running through your dirty data, look for the synonym in the tlChemSyn table, and take the FK into the field in your target table - which should also have a FK to the tblChemical.

This will mean that you will have to build the synonym library. I would take the dirty data and do an append query of the dirty chemical name field into the tblSynChem, and then go through and select the proper chemical that matches by hand. Every time you get new data, do this first (append more rows) and then make the match, and then import your data.

ETL is not fun, but sometimes necessary. Maybe someone here can post a more elegant solution.

If you post the source and target structures, I can help you write the code to run this routine. Of course it will need error checking.

Did this help any?


mmcdonal

The data sources are going to be .csv and/or tab-delimited .txt files that are sent in from various labs. I'll do up the basic db table structure and post it here when I get it mocked up. Thanks for the assist.

OK, here's the table structures for the db (fields separated by dashes):

-----------------------------

tblAnalytical:
ChemicalID-Qualifier-Result-DetectionLimit-Units-AnalysisDate-LabID-Method

tblSamples:
SampleID-Depth-SampleDate-ParType-LabID

tblChemicals:
ChemicalID-ChemicalName

tblChemSyn:
ChemSynID-ChemicalID-Synonym

Keys/table relationships are set up as suggested

-----------------------------

And here's the table structure for the import files:

SampleID-Depth-SampleDate-ParType-ChemicalName-DetectionLimit-Qualifier-Result-Units-AnalysisDate-LabID-Method

Explanation of fields, in case it's useful:

SampleID = sample name (ie. BH-1)
Depth = sample depth (in feet, soil samples specific, ie. 2-4)
SampleDate = date of sample collection
ParType = type of sample (ie. soil, water or vapor)
ChemicalName = chemical name (multiple names per chemical)
DetectionLimit = max concentration that can be detected by lab
Qualifier = < or blank, used if result is less than detection limit
Result = concentration of chemical in sample
Units = units of concentration ug/kg = parts per billion
AnalysisDate = date lab ran the sample
LabID = unique lab identifier for each physical sample
Method = laboratory method used to detect chemical

As I mentioned before, the import files are .csv and tab-delimited text files that are sent in by the various labs. The good news is that the labs are consistent in how they report their data, so the synonym dictionary should only need updated if the lab changes formats or a new lab is used. Thanks again for the help.

First of all, I see you have a LabID FK in some tables, but no lab table. Do you just refer to each lab by that ID? Also, you use the PK field name "ChemicalID" twice. I see it is really an FK in tblAnalytical, so add an AnalyticalID PK field to that table.

Secondly, where is the SampleID coming from? Is that in the data in the csv file? Or are you supplying that in the table? If it is in the csv file, you should add one to your table as well since you can't control ID's from outside sources. That would be for tblSamples.

So you are bring data into this table which you don't name. Let's call it tblImport:

SampleID-
Depth-
SampleDate-
ParType-
ChemicalName-
DetectionLimit-
Qualifier-
Result-
Units-
AnalysisDate-
LabID-
Method

Then you are moving this data to other tables? Is that correct? If so, do you want to delete the data in the tblImport table?

Anyway, create a query to find unmatched chemical names in your incoming data so you can add them to the tblChemSyn table:

SELECT tblImport.SampleID, tblImport.Depth, tblImport.SampleDate, tblImport.ParType, tblImport.ChemicalName
FROM tblImport LEFT JOIN tblChemSyn ON tblImport.ChemicalName = tblChemSyn.Synonym
WHERE (((tblChemSyn.Synonym) Is Null));

This will show you any NEW chemical names coming in from your labs that are not already in the ChemSyn table, where you will be able to add them. If nothing shows up in this query, then no new values need to be added. You will want to do this by hand as you bring in new data, but as you say, once this table is populated, it is not likely to change.

More on next post...







mmcdonal

Just a note, I put a few sample columns in the find unmathed query, but you won't need to add that many.

Now, from the data in tblImport, it seems you want to do this with it:

tblImport
SampleID to tblSamples
Depth to tblSamples
SampleDate to tblSamples
ParType to tblSamples
ChemicalName to tblAnalytical (change to ChemicalID)
DetectionLimit to tblAnalytical
Qualifier to tblAnalytical
Result to tblAnalytical
Units to tblAnalytical
AnalysisDate to tblAnalytical
LabID to tblSamples, tblAnalytical
Method to tblAnalytical

I don't see how these two tables are related. tblSamples and tblAnalytical share no common fields. Do you just want to parse these fields into each table without reference to one another? You don't even keep the SampleID field in tblAnalytical, so there is no way of relating these two tables. How do you want to handle that?





mmcdonal

LabID is assigned by each laboratory as a unique ID for every sample that we send them from each sampling event. It basically equates to their version of our SampleID for the particular group of samples we've sent in. LabIDs are formatted as such 10000-01, with the last two digits increasing with each sample (ie. MW-1 = 10000-01, MW-2 = 10000-02, MW-4 = 10000-03, etc.). The next sampling event may result in LabIDs like: 120330-01, 1393433-01, etc...never sharing the same set of initial numbers (these are based on the unique number printed on each chain of custody form).

SampleID is based on an actual sampling location on a project site, like monitoring well #1 (MW-1),for example. It's also in the .csv file from the lab. We designate these on the laboratory chain of custody when we send samples in for analysis. I couldn't use either SampleID or LabID by themselves as primary keys in tblAnalytical because multiple chemicals are listed for each (ie. benzene, toluene and MTBE present in each sample (SampleID/LabID) and individual SampleIDs appear a number of times in the db for multiple sampling dates. I chose to use LabID as the primary key in tblSamples instead of SampleID in case a sample location is destroyed, replaced and mistakenly given the same name in the future (ie. MW-1 replaced as MW-1 instead of MW-1R, both the old location and new location must have unique names for mapping). Right now, tblAnalytical uses both the ChemicalID and LabID fields in a composite key. I figured it was just as good as creating another autonumber ID field. However, I have no problem setting up another field to act as the primary key.

You are correct that I'd like to split the import data into the appropriate tables. Once the import has been completed, tblImport can be deleted.

If possible, I'd like to set the db up so that Access will prompt the user during the import process when a chemical is not recognized. Is that possible?

tblAnalytical and tblSamples are related based on the LabID field in a many to one relationship as a result of LabID being basically equal to SampleID as mentioned above.

Let me first clarify that this: "multiple chemicals are listed for each (ie. benzene, toluene and MTBE present in each sample" does not mean that in the field for Chemical you will not have an entry that looks like "benzene, toluene", but will have two records with "benzene" in one, and "toluene" in the next. Which case is it?

We can do all this, except having two chemical names in one record is dicey.


mmcdonal

Each recordset will have have one chemical listed. In my previous post, I meant to paint a picture like this:

MW-1,Date1,Benzene,LabID1-1
MW-1,Date2,Benzene,LabID2-1
MW-1,Date3,Benzene,LabID3-1
MW-1,Date1,Toluene,LabID1-2
and so on.

Sorry about the confusion.

Here is the first part of this. I am under a time crunch today:

Create a delete query to remove data from the Import table. You will run this query everytime you do something with data. Call it qryDELETEImports

When importing data, run the delete query first to make sure there is no residual data in the table.

Then import the data into the import table.

Then run code to take the Chemical name data from the Find Unmatched chemical names query, and push them into the sysnonym table. Then create a second query that opens based on the synonym field having and value, and the chmicalID field being blank. This is the step you asked for to prompt the user to create these relationships. This will force them to do this before they move the data over to the other tables. Basically you run the query, and if any rows are returned, you branch to that process instead of finishing the imports to the other tables. If that recordset is empty, then branch to finish the import process.

Can you get this working up to here? I have to go to a meeting and then to school. More tomorrow then.


mmcdonal