Strip data from one field

[emikoo]

Hi,

Can not figure this one out, any help would be appreciated:

In an access file I linked a table, via ODBC, to an external database. In the table there is one field ("State&quotwhich contains a sequence of 1440 characters. Each character describes the state of an employee during one particular day (24hrs*60minutes=1440). A sample record may look like this:

Name: State:
John OOOOOOOOOO

http://WWWWWWOOOBBBSSS

etc.etc.

I need to find a way to make a total count per state. Results should be similar to:

Name: State: Count:
John O 13
John W 6
John B 3
John S 3

Any suggestions?
Thnxs,

EK

 

[mymou]

Hi emikoo

Is possible to create a function to go through the string - but this would be a bind.

An alternative - even if less efficient solution - would be to separate all the data into a temp table and then query that table.

Sub Separate()

DoCmd.SetWarnings False

For Count = 1 To 1440
strsql = "INSERT INTO Temp ( name, state )" & _
" SELECT Original.name, mid([State]," & Count & ",1) FROM Original;"
DoCmd.RunSQL (strsql)
Next Count

DoCmd.SetWarnings True


End Sub

Then query that temp table

SELECT Temp.name, Count(Temp.state) AS CountOfstate, Temp.state
FROM Temp
GROUP BY Temp.name, Temp.state;


Easy, but inefficient. Depends what your needs are.

Stew

 

[emikoo]

Stew,

Thnxs a lot! Does the job.

EK