Query To See If text string exists in a field

[schwallyhoo]

I have a query which for simplicity we will say, although it has multiple fields, the principle
would be the same.



ID, AreaCode (both text fields)

In the form

"TP21","6102"
"TP34","6102,6205"
"TP65","6205"
"TP54","6102,6205,6107,6209"

As you can see, some locations will appear more than once, and what I need to do is run a query so I
can pick up every instance of TPXX for location 6102 and 6205 etc.

I have a master list of location codes and I imagine the best way to do it would be to run through
the location codes sequentially, use some sort of "does it exist" in the location string and if so
append it to a table, cycling through all of the locations?

Thanks

J

 

[Bob Barrows]

I have a query which for simplicity we will say, although it has
multiple fields, the principle would be the same.



ID, AreaCode (both text fields)

In the form

"TP21","6102"
"TP34","6102,6205"
"TP65","6205"
"TP54","6102,6205,6107,6209"

As you can see, some locations will appear more than once, and what I
need to do is run a query so I can pick up every instance of TPXX for
location 6102 and 6205 etc.

I'm not sure what you mean by "etc", which means "and others" What "others"?
The entire list of possible area codes? If so, no need to filter the data at
all ... just select them all.
That seems too simple, so I am going to ignore the "etc" for now.

I have a master list of location codes and I imagine the best way to
do it would be to run through the location codes sequentially, use
some sort of "does it exist" in the location string and if so append
it to a table, cycling through all of the locations?

Err ... no
I'm puzzled. You have a list of all the possible location codes, correct? So
it would appear that you want to simply select all the records ...
That makes no sense ... wait! I think I see what you want (it would have
been helpful if you had provided your desired results in tabular form the
way you presented your sample data):

AreaCode ID
6102 TP21
6102 TP34
6102 TP54
6205 TP34
6205 TP65
6205 TP54
6107 TP54
6209 TP54


To get only the records where AreaCode contains "6102" you would use LIKE
with a wildcard criterion:

Select ID,AreaCode FROM yourtable
WHERE AreaCode LIKE "*6102*"

This might give the wrong answer if an area code like 361025 could exist. I
doubt that's a possibility, but if I'm wrong, you need to include the commas
in your criterion, like this:

Select ID,AreaCode FROM yourtable
WHERE "," & AreaCode & "," LIKE "*,6102,*"

To use the master table, you need to use what is called a "non-equi" join,
which can only be created in the SQL view of a query builder. You need to be
careful never to switch to Design view, because your join criteria will be
lost. Anyways, the query would look like this:

SELECT m.AreaCode, y.ID
FROM MasterTable as m JOIN yourtable as y
ON y.AreaCode LIKE "*" & m.AreaCode & "*"

If yourtable has a lot of records in it, you are going to find performance
to be pretty poor. This comes down to your design decision to store data in
comma-delimited lists instead of in records where they belong. This query
would have been both easy to write and quick to perform if you had used the
correct design for yourtable in the first place. The correct design looks
pretty much like the desired results I provided above. If you put an index
on the AreaCode field in that design, it pretty much would not matter how
many records there are.

 

[schwallyhoo]

THe problem I have is that the master table already exists, and the location data is a new addition I have been asked to do. Unfortunately there
are multiple locations for some ID's and somebody else did the typing. I know there are other workrounds, but all the locations are unique, so O
only need to use Like "*"loctxt"*" for example to filter all instances of that ID for the relevant location. There are only 900 ish records and
these will reduce as items complete, which is why I want to create a table and update on a weekely basis filtering out the compelted items.

The folowing subroutine will step through the location table.


Private Sub comprepcombut_Click()
On Error GoTo Err_comprepcombut_Click


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim compotxt As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Compartment")

Do Until rst.EOF

compotxt = rst!CompartmentText


*******************************************************************************************************

REM: This is where I probably need to use docmd.runsql with an "insert into" SQL statement to pull out the filtered data from the master table
along the lines of:

SQL_TXT = "INSERT INTO tbl_Compartment_Data * WHERE CompartmentText = compotxt" then use the Docmd.runsql facility to loop through the
compartment table and append the tbl_Compartment_Data where the criteria is met.

I am struggling to find the right syntax for such a statement at the moment. compotxt and CompartmentText are both string data

******************************************************************************************************


rst.MoveNext

Loop


rst.Close



Exit_comprepcombut_Click:
Exit Sub

Err_comprepcombut_Click:
MsgBox err.Description
Resume Exit_comprepcombut_Click

End Sub

 

[Bob Barrows]

THe problem I have is that the master table already exists,

That's not relevant to my point.

and the
location data is a new addition I have been asked to do.

So, why not do it correctly?

Unfortunately there are multiple locations for some ID's and somebody
else did the typing.

OK, so this will be a one-time conversion into the correct form for the
data.

I know there are other workrounds, but all the
locations are unique, so O only need to use Like "*"loctxt"*" for
example to filter all instances of that ID for the relevant location.
There are only 900 ish records and these will reduce as items
complete, which is why I want to create a table and update on a
weekely basis filtering out the compelted items.

The folowing subroutine will step through the location table.

Not needed. Simply convert my SELECT statement into a make-table
SELECT...INTO query:

SELECT m.AreaCode, y.ID
INTO newtable
FROM MasterTable as m JOIN yourtable as y
ON y.AreaCode LIKE "*" & m.AreaCode & "*"

Add criteria for "Completed" and you're done.

 

[schwallyhoo]

Bob,

I couldn't get the SQL to work, keeps coming up with a Syntax error in the FROM clause.

The code below I have attached to a command button and it is generating the required table, albeit slowly. However, there is still an issue with
it as even though it is filtering the data correctly using the LIKE clause, I will need to insert the compotxt as a field into the output table, as
currenty it shows all the locations applicable. Currently reviewing vis google to see if I can add the compotxt into the output table along with
the select fields

Regards

J


Private Sub comprepcombut_Click()
On Error GoTo Err_comprepcombut_Click


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim compotxt As String
Dim SQL_txt As String



Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Compartment")


Do Until rst.EOF

compotxt = rst!CompartmentText

SQL_txt = "INSERT INTO tbl_Compartment_Data " & "SELECT * " & "FROM [qry_Outstanding_Completions] WHERE [Compartment] Like '*" & [compotxt] & "*';"

DoCmd.RunSQL SQL_txt

rst.MoveNext

Loop


rst.Close

'MsgBox "Recordset closed"


Exit_comprepcombut_Click:
Exit Sub

Err_comprepcombut_Click:
MsgBox err.Description
Resume Exit_comprepcombut_Click

End Sub