Split field in query using a semicolon separator 1

[darall]

I have data that is pulling into an Access database from another source. In this table there is a field called Tasks which is made up of four pieces of data separated by semi-colons.

I do not have the control over the database or data to change how the pull is setup I can only query on the table that is created for me by the owners of this data. I need to be able to report on each level in the Tasks field.

I tried using the left, right and mid functions but since the length varies it would not work.
The number of records is too large for Excel to handle or I would pull it into Excel do a Text To Columns and pull it back. I could do this in large groups but I have to report this data weekly and this would be a labor intensive process every week.

So my question, is there a way to separate this field in a query by using the semicolon?

Below is a sampling of data from the tasks field:

[UL]

[li]Client and Peripherals Sustaining - FY07;Infra Enhancements;Corporate;CORP[/li]
[li]Client and Peripherals Sustaining - FY07;Infra Enhancements;Corporate;EIT[/li]
[li]Client and Peripherals Sustaining - FY07;Infra Enhancements;Global[/li]
[li]Corp/Cross BU - Data administration Sustaining - FY07;Enhancements;Corporate;CORP[/li]
[li]Corp/Cross BU - Data administration Sustaining - FY07;Enhancements;Corporate;EIT[/li]
[li]Corp/Cross BU - Services Maintaining - FY07;Break Fix;Analysis/Fix[/li]
[li]Corp/Cross BU - Services Maintaining - FY07;Break Fix;On-Call[/li]
[li]Day Job 2007 - ES App Services;App Services[/li]
[li]Day Job 2007 - ES App Services;Assessment[/li]
[li]Day Job 2007 - ES App Services;Resource Management[/li]
[li]EIT-TS Active Directory - Phase II;01-Planning- International - NonCap[/li]
[li]EIT-TS Active Directory - Phase II;02-Post Deployment Support - NonCap[/li]
[li]EIT-TS Active Directory - Phase II;03- Deployment - Cap[/li]
[li]EIT-TS Active Directory - Phase II;04-Migration Planning- Data Prep- Exp[/li]
[li]EIT-TS Active Directory - Phase II;05-Project Management - Cap[/li]
[li]EIT-TS Active Directory - Phase II;Application Remediation- Cap[/li]
[li]EIT-TS Active Directory - Phase II;Application Testing- Cap[/li]
[li]EIT-TS Active Directory - Phase II;Migration Deployment ME-Cap[/li]


[/UL]

Thanks for the help!
Denae

 

[lespaul]

just because the original data is stored that way, doesn't mean that you have to keep it that way. you can import that (badly formatted) information into your own table having each of the entries split into a single field. Look into the SPLIT function that will allow you to declare what the delimiter is (the semicolon) and then insert the information into a (correctly designed) table to do your work.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[darall]

Leslie,

Thanks for the info, however I was hoping for a more automated way. I would have to re-create the table with the split data every time we wanted fresh data which is several times a week. I also need to be able to pass this on to folks who want to run the query ad-hoc and don't have the skills to re-create the table before running the query.

Is there a way to do a split on a field in a query?

Much appreciated,
Denae

 

[PHV]

In a standard code module create the following function:

Public Function getElem(str, delim As String, N As Integer)
If IsNull(str) Then Exit Function
Dim myArr
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = Trim(myArr(N - 1))
End If
End Function

And now you can use it in SQL:
SELECT getElem([task field], ';', 1) AS part1
, getElem([task field], ';', 2) AS part2
, getElem([task field], ';', 3) AS part3
, getElem([task field], ';', 4) AS part4
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886