Extracting Information from a String

[crmulle]

I am importing a string of text that includes multiple values for a given
record. Some of the records extend beyond the 255 character limit and this
truncates the field. What I would like to do is have a formula that looks at
the string to decide if certain variables are in the string. If those
variables appear in the string then set the text box to yes for that
particular variable. There are more than 50 variables assigned to this field.

Example of incoming data:

VendorID VendorName ServiceCategory
2222222 Test Company Printing; Recycling; Deposits; Wealth Managment

Example of How I Want the information in Access:

VendorID VendorName Printing Recycling Deposits Wealth
Management
2222222 Test Company Y Y
Y

My thought was to create an if statement but I need some direction. Any
help would be appreciated.

Thank you,
Carolyn

 

[Dorian]

THis is probably not what you want to hear - but - your database design is
not normalized. Each column should be atomic - meaning containing only one
item of information. You really need to redesign your tables to have the
Service categories in a separate table.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[crmulle]

Dorian,

Thank you for your comments.

Yes, I agree that service categories would need to be into it's own table.
My issue is the incoming data from a string of text that has multiple values
for a given record and pulling each value from the string and marking True
for approriate value field for the given record.

Carolyn

 

[John W. Vinson]

I am importing a string of text that includes multiple values for a given
record. Some of the records extend beyond the 255 character limit and this
truncates the field. What I would like to do is have a formula that looks at
the string to decide if certain variables are in the string. If those
variables appear in the string then set the text box to yes for that
particular variable. There are more than 50 variables assigned to this field.

Example of incoming data:

VendorID VendorName ServiceCategory
2222222 Test Company Printing; Recycling; Deposits; Wealth Managment

Example of How I Want the information in Access:

VendorID VendorName Printing Recycling Deposits Wealth
Management
2222222 Test Company Y Y
Y

My thought was to create an if statement but I need some direction. Any
help would be appreciated.

It makes a cruicial difference whether there is a fixed, predictable set of
ServiceCategory values, or whether they are freeform.

I am pretty sure that *eventually* you will want neither of your
non-normalized structures, but rather a three table arrangement:

Vendors
VendorID <primary key>
VendorName

ServiceCategories
ServiceCategory <text, Primary Key>

VendorCategories
VendorID
ServiceCategory <joint two field primary key>

The third table would have records like
2222222 Printing
2222222 Recycling
2222222 Wealth Management
2222223 Database design
2222223 Computer tech support

To extract the information from your current table you'll need to use the
Instr() function to find each. To directly answer your question (though I
really don't think it's a good destination!!) you can use a query:

SELECT VendorID, VendorName,
IIF(InStr("Printing", [ServiceCategory]) > 0, "Y", Null) AS Printing,
IIF(InStr("Recycling", [ServiceCategory]) > 0, "Y", Null) AS Recycling,
IIF(InStr("Deposits", [ServiceCategory]) > 0, "Y", Null) AS Deposits,
IIF(InStr("Wealth Management", [ServiceCategory]) > 0, "Y", Null) AS [Wealth
Management])

and so on.

 

[crmulle]

John,

Thank you very much for your direction. I agree with your table arrangement
and understand the query. Thank you so much!

I am importing a string of text that includes multiple values for a given
record. Some of the records extend beyond the 255 character limit and this
truncates the field. What I would like to do is have a formula that looks at
the string to decide if certain variables are in the string. If those
variables appear in the string then set the text box to yes for that
particular variable. There are more than 50 variables assigned to this field.

Example of incoming data:

VendorID VendorName ServiceCategory
2222222 Test Company Printing; Recycling; Deposits; Wealth Managment

Example of How I Want the information in Access:

VendorID VendorName Printing Recycling Deposits Wealth
Management
2222222 Test Company Y Y
Y

My thought was to create an if statement but I need some direction. Any
help would be appreciated.

It makes a cruicial difference whether there is a fixed, predictable set of
ServiceCategory values, or whether they are freeform.

I am pretty sure that *eventually* you will want neither of your
non-normalized structures, but rather a three table arrangement:

Vendors
VendorID <primary key>
VendorName

ServiceCategories
ServiceCategory <text, Primary Key>

VendorCategories
VendorID
ServiceCategory <joint two field primary key>

The third table would have records like
2222222 Printing
2222222 Recycling
2222222 Wealth Management
2222223 Database design
2222223 Computer tech support

To extract the information from your current table you'll need to use the
Instr() function to find each. To directly answer your question (though I
really don't think it's a good destination!!) you can use a query:

SELECT VendorID, VendorName,
IIF(InStr("Printing", [ServiceCategory]) > 0, "Y", Null) AS Printing,
IIF(InStr("Recycling", [ServiceCategory]) > 0, "Y", Null) AS Recycling,
IIF(InStr("Deposits", [ServiceCategory]) > 0, "Y", Null) AS Deposits,
IIF(InStr("Wealth Management", [ServiceCategory]) > 0, "Y", Null) AS [Wealth
Management])

and so on.

 

[John W. Vinson]

John,

Thank you very much for your direction. I agree with your table arrangement
and understand the query. Thank you so much!

What you might want to do to get directly to the normalized table structure is
to first create your ServiceCategories table, with one text field with these
four (or all possible) values that will occur in the semicolon delimite field.
You can then create a "Cartesian Join" query:

SELECT yourtable.VendorID, ServiceCategories.ServiceCategory
FROM yourtable, ServiceCategories
WHERE yourtable.ServiceCategory LIKE "*" & ServiceCategories.ServiceCategory &
"*";

to populate the PartCategories table. This query will generate multiple
records for each record in your table.

 

[siqueira veronica]