Partial Query

[roc100]

All,
I am fairly new to Access. I have set up a query using a left join to compare two tables and return the data I am looking for. It works perfect for exact matches, however there are certain data items that need to match using partial data. For example

LMA 546413216 (the numbers change each month)
needs to match with
LMA
to return
"LMA bank"

There are about 100 of these special circumstances. I have tried using an INSTR function with the join but that is too general and compares everything and returns the wrong values. I had suceess entering
Like "LMA *" in the where claus - however that excluded all other data and did not return the "LMA BANK" data that I needed.

Here is what I have that works for exact data - I am thinking I need to add the Like "LMA *" to the join statement somehow for values that are null. Again, there are many of these instances so I would need to add over 100 of these like statements. Which is fine if it will work. Any help is greatly appreciated!

FROM Cash_Reconciliation LEFT JOIN [TBL DESCRIPTION] ON Cash_Reconciliation.Description = [TBL DESCRIPTION].DESCRIPTION

 

[plog]

I would create a mapping table. Essentially you have 2 tables, that kind of match but not really. What you do is instead of directly linking them you create a third table that explicitly tells which values to link in each table and then join them with that in the middle.

For example:

Table One:
'LMA'
'XYZ'
'ABC'

Table Two:
'LMA bank'
'XYZ'
'ABC def'

Mapping Table:
'LMA', 'LMA bank'
'XYZ', 'XYZ'
'ABC', 'ABC def'

When you go to write your query you link Table 1 to the Mapping Table and the Mapping Table to Table 2.

 

[MSAccessRookie]

While mapping might work in a small Table, it will get cumbersome as the Table grows. You might want to consider the LIKE command.

http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx

It should have the poitential to do what you want to do