matching part of the string from two columns

[hoachen]

Table: Info

CustID Cust Name Item
1235 Middle Steve Cust 55
2345 Boss AG 66
1142 kate spade new york 77
4666 Wally Bags 58
6868 LAUREN by Ralph Lauren 56

Table:Qty

Item# Description name Condition Qty
55 kate spade new york New 5
55 Steven by Steve Madden Ltd New 2
55 MPS Used 7
77 West Used 6
77 Chocolate New York New 8

Query1 (Combine above two tables)

 Item# CustID Cust Name Qty Description Name Condition
55 1235 Middle Steve Cust 7 MPS Used
55 1235 Middle Steve Cust 2 Steven by Steve Madden Ltd New
55 1235 Middle Steve Cust 5 kate spade new york New
66 2345 Boss AG 
77 1142 kate spade new york 8 Chocolate New York New
77 1142 kate spade new york 6 West Used
58 4666 Wally Bags 
56 6868 LAUREN by Ralph Lauren

This is not what I want to display on the query. I want to know if it possible to have this output on the query. I am not sure query will be able to do it or need to use vba.

I want whenver the description match some word on the Client name put it on one column and the rest sum up and display on another column. The query should look like this:

Matching table

1 & 2 Item# CustID CustName Match the word The rest which sumup
55 1235 Middle Steve Cust 2 12
77 1142 kate spade new york 8 6
66 2345 Boss AG 
58 4666 Wally Bags 
56 6868 LAUREN by Ralph Lauren 

Is this possible to do?

[aytee111]

Yes. It will be complicated however. It would be better to not repeat information across tables, then you won't have to find a match. For instance, your Info table and your Qtys table both contain the customer name. That is a big NO. Only the id should be carried on other tables, not the name. The two tables are then joined by CustID. You might want to separate out the location and put it in its own column on the Info table.

[hoachen]

Thanks for your time for reading my post.

However, the "Cust Name" on Info Table is different from Qty table on "Description name". Both tables can be linked by item#. That is the issue I have is to match partial word that on "Cust Name" to "Description Name".

[aytee111]

If the two tables are being linked by Item # then why are you worrying about matching the descriptions? If you know 77 is customer 1142 then the name can come from the info table, regardless of what the name is on the Qty table.

There is no way to magically match two strings of data. One idea I can think of is to match in strings of 5 letters at a time, but that is too restrictive and could get quite hairy. I think you need to rethink your data and the way it is being captured.

[hoachen]

If the two tables are being linked by Item # then why are you worrying about matching the descriptions? If you know 77 is customer 1142 then the name can come from the info table, regardless of what the name is on the Qty table.

There is no way to magically match two strings of data. One idea I can think of is to match in strings of 5 letters at a time, but that is too restrictive and could get quite hairy. I think you need to rethink your data and the way it is being captured.

Ok, thanks