Query based on partial matches between fields

[J N Winkler]

I am using Access 2000 and seeking help designing a query which will select records based on a partial, rather than a complete, match between fields.

I have two tables, A and B. A is essentially a complete listing of state highway projects in Arizona and contains the following fields:

Route
TRACS
Federal-aid
Project description

B has just one field:

Federal-aid

I want to produce a query which contains all the rows in A whose Federal-aid entry has no partial matches with any Federal-aid entry in B.

The problem is that the Federal-aid entries in A and B are formatted somewhat differently. Those in B have no leading alphabetic characters: e.g., 200(805), 17-1(116). Those in A do have leading characters, which are funding codes: DE200(805), I17-1(116). As an example, my query should match "17-1(116)" in B with "I17-1(116)" in A and keep the corresponding row of A out of the results.

The funding codes are of uneven width (ranging from 1 up to 4), so the Left operator is not an option. The partial matches will be perfect on the right (i.e., the A entries do not also have trailing unmatched characters to worry about). Each instance of B will occur just once in A.

I realize one option would be to preprocess the A data to remove the funding codes, but I would rather avoid this if I can. I am a SQL novice, however, so I'd be much obliged for any help structuring the query so it will do what I want. Several Google searches have turned up answers to variations of this question, but I have had trouble parsing the highly abbreviated syntax that is given.

 

[wilpeter]

Just a question: in table B, is Federal-Aid the only field or is there also an autonumber or id field? If yes, is it a Primary Key field? Did you, for example, reach your current status by selecting the value for that field in table A from a combo box? Even parsing, with varying quantity of lead alpha characters, is not easy with Excel. Still thinking.

 

[Brianwarnock]

The problem is that you cannot look for Not Like as there will loads of records that fit that criteria, so you use 2 queries the first uses like to pull all records where Tablea has a match or partial match in tableb
eg

SELECT tablea.Federal-aid
FROM tablea, Tableb
WHERE (tablea.Federal-aid) Like "*" & [tableb].[Federal-aid];

Then outer join Tablea to the query as a find unmatched query
eg

SELECT tablea.Federal-aid
FROM tablea LEFT JOIN Query1 ON tablea.Federal-aid = Query1.Federal-aid
WHERE (Query1.Federal-aid) Is Null;

Brian