Wildcard and jumbled text (1 Viewer)

[Thomp001]

I am importing data from another database I can't control in which I am getting data in a field that has text data all jumbled in together. Ex..ABC, ABC123, 123XYZ..The data needs to be seperateed out into like catagories. If I run a wildcard that is *ABC* I would see all the data that has ABC in it

Which is great except I need to change the Wildcard query results into a field that will change all of the data into one single catagory.

Ex..ABC, ABC123 will need to become ABC for all the data in the field.

I actually need to have several different wildcards to pull out all the possible combinations of data that is jumbled into my field. Is their an iif statment or something that will help me pull out this info.

I hope this makes sense as it is really hard to explain in a message

 

[Jon K]

Add a Category field in the table.

In an Update Query, type this setting in a column in the query grid (using the correct field name of the jumbled text field):-

Field: Category
Update To: IIf([JumbledField] Like "*ABC*","ABC", IIf([JumbledField] Like "*XyZ*","XYZ", [JumbledField]))

 

[Thomp001]

wildcard and Jumbled text

Jon,
Thanks for your response. I have just one concern. If I run this querry and I have the jumbled text ABC123 in one of my records and then run this querry as an update querry with both the wildcards "ABC" and "123". Will the querry return two records now for ABC and 123 or will it just change the table to the first wildcard of ABC.

I need to account for both instances of ABC and 123 as I am trying to seperate out the text.

Thanks,
Thomp

 

[Jon K]

It depends on the position of the wildcard in the Update To expression. The wildcard that comes first will have precedence.

So if the Update To expression is:-

IIf([JumbledField] Like "*ABC*","ABC", IIf([JumbledField] Like "*XYZ*","XYZ", IIf([JumbledField] Like "*123*","123", [JumbledField])))

ABC has precedence over 123. So ABC123 will produce ABC in the Category field, and so will 123ABC.

 

[Thomp001]

Is there a way to create a querry that will account for both instances of the jumbled text. I need to analyis how many instances I have of both ABC and 123 in my records. If my querry overwrites the ABC123 with ABC I won't be able to also sort out my counts for 123 also.

Thanks,
Thomp

 

[Jon K]

Since ABC123 needs to be counted twice, the above Update Query will not work.


What you need is a Union Query to prepare a table for analysis. The SQL statement of the Union Query goes like this (it would be easier to directly type the statement in the SQL View of a new query):-

SELECT Fiedl1, Field2, Field3, Field4, JumbledField, "ABC" as Category
FROM TableName where JumbledField like "*ABC*"
UNION
SELECT Fiedl1, Field2, Field3, Field4, JumbledField, "XYZ" as Category
FROM TableName where JumbledField like "*XYZ*"
UNION
SELECT Fiedl1, Field2, Field3, Field4, JumbledField, "123" as Category
FROM TableName where JumbledField like "*123*";


You can then make your analysis based on this Union Query.