Searching for multiple strings 1

[nlbertram]

I have a query returning several records where Points=5.

Table: old_table
Query: old_query

ID Number Points
1 876 5
2 345 5
3 989 5
4 112 5
5 394 5
6 834 5


I would like to search for the records in new_table that meet the condition where Number from old_query is contained in the Name field.

Records 1, 2, 3, 4, 5, 8 would be returned in this example.

Table: new_table

ID Name
1 876-sed
2 876-lkm
3 876-pun
4 112-xyz
5 394-pqr
6 333-abc
7 888-abc
8 394-dfg


Any ideas?

 

[PHV]

A starting point (SQL code):
SELECT *
FROM old_table O, new_table N
WHERE N.Name Like O.Numbers & '-*' AND O.Points=5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nlbertram]

Thanks for the tip. However, I am only able to use the most basic SQL statements when working in my particular Access DB. When I use your code the DB hangs and doesn't finish processing. I believe it has something to do with the number of records (1.5 M).

VBA code seems to work the best for my given situation, however, I'm not exactly sure how to do it.


-

 

[MajP]

VBA code is almost never faster than pure SQL. There may be some database design issues, or you may consider a SQL server backend.

However I would try updating the new table. Add a field to the new_table newNumber. Then run an update query/queries to get the number. Your example shows three numbers then text, but since you have a million I assume your rule is a "number- text"

This is actually less efficient than PHV's, but if it does work then you will have a permanent, efficient means to join the tables.

UPDATE new_table SET new_table.newNumber = Left([name],InStr([name],"-")-1);

 

[nlbertram]

Yeah...you are right about the DB design issues. I'll have to investigate that further. In the meantime, the VBA code may be a quick alternative.