Linking two tables via with a wildcard

[Murraylr]

Hi all,

I'm very new to MS Access & I am totally stuck. I have searched the web & youtube but I can't find something to match what I'm trying to do. Saying that my poor terminology is probably not making life easy.

Anyway, I have two data tables. One being the main data source & the other is just a lookup table. So I want to add an extra column to the source data from the lookup table, however I can't do a straight link because the lookup references are different. The lookup table has a 'begins with' col, so ideally I'm looking to find a way to join all data that begins with col1 of the source data against col1 of the lookup & add col2 of the lookup into the source data. For example:=

Source Data


Col1
ABCDEF,
ABCDD1,

Lookup Data
Col1, Col2
ABC , Test 1
ABCDD , Test 2

OUTPUT OF SOURCE
Col1, Col2
ABCDEF, Test 1
ABCDD1, Test 2


I'm really sorry if this is an easy ask, but I just can work out how to do it.

Cheers
Lee

[Rawb]

For you to directly link the Tables in the manner you want, your "begins with" field will need to be one of 2 things:

1. an exact match for the data in your other table (no wildcards), or
2. always exactly the same length

You have a few more options if the data will never change in either of the tables once it's been inserted, but if any of the data can be changed/updated the best you'll probably be able to do is come up with a VBA function that does the matching and writes the data to a temporary table.

NOTE: With item 2 above, you still can't create a DIRECT link between the two tables, but you can at least create a Lookup Query to get the data you want instead of having to write a VBA function for it.