Wildcard problem in joined query

[Joan]

I have a table that uses a wildcard in the Part No. field,
for example, the part no. is 36701*.

I have another table that uses the actual number (no
wildcard), for example, the part nos. are 36701W, 36701Q,
36701L.

I am trying to join the tables in a query, using the part
numbers from the first table to pull additional info from
the second table. Of course, it doesn't find a match
because one table says 36701* and the the other says
36701W.

I am trying to find out if there is any way to make Access
understand that the * in the first table should be seen as
a wildcard, and therefore find matches in the second table.

To make matters more complicated, sometimes the wildcard
is not at the end, for example, 36701*P. The information
in the first table comes from parts manuals that I have
saved as text and imported. I could do something to the
numbers as I import them, but I'm not sure what.

Any ideas would be appreciated!
Thanks
Joan

 

[Pete]

I have to assume there is a one-to-one correspondence
between the letter suffixes and the wild card entries--in
other words to use your example, you want all 37601*'s to
be viewed in the query as 37601W's, not 37601W's some
times and 37601Q's at other times. Otherwise how would
it work? Anyway, I'll be interested if someone stronger
at Access can help you directly in Access, but I think
you can use Excel very quickly to get the results you
need if my above assumption is correct. Just highlight
that column with all the wildcard data and paste it into
an Excel spreadsheet. Then choose DATA > TEXT TO COLUMNS
and you can either use FIXED WIDTH if all instances are 5
digits as in all of your examples, or choose DELIMITED
and put an asterisk in the OTHER box to split off the *
plus suffix as needed.

Let's say that column A in this spreadsheet has the 5
digit numbers now. Then you can paste the column with
the non-wild card version alongside this column in the
spreadsheet, and use the same function to temporarily
split off the letter suffixes. For both columns, create
numbered indexes which consecutively number all their
rows, (put a 1 in row 1, then =A1 +1 in cell A2, then
copy it to the bottom) and do it as a value copy of this
onto itself so so that the formulas are dead and all you
have is values--so that sorting and resorting will work.
Then name a range for this second split off prefix
column, and do =VLOOKUP
(A1,prefix_column_range_name,1,false). copy this down
the whole column, value copy this onto itself, and
replace column A with this. Then do a Concatenate of
each item in this new column, with the suffixes you split
off of the non-wild card column. Then use the index you
created to resort column A and put it back into Access.

Hope this helps!

 

[John Spencer (MVP)]

Partial solution would be to open your query in SQL view and change the join
criteria from equals to like

Change
On TableA.PartNo = TableB.PartNo
To
ON TableA.PartNo LIKE TableB.PartNo

TableB should be the one with the asterisk (*) in the part number.

You can also join on a part of the two numbers

ON LEFT(TableA.PartNo,5) = LEFT(TableB.PartNo,5)

That would match on the first 5 characters of the two part numbers.

Your best solution might be to split the part numbers into two parts - a primary
number and a subPartNumber. The primary number would be the part that should
match 36701 and the subpartNumber would be the remainder of the number.

That