Make Table from un-normalized table! 1

[proximity]

Hi,

I have inherited an Access 2002 database that stores data in a table called tblJobs. The data is stored in 3 fields and an example of the data is shown below:

JobTask | StartTime | EndTime
MPK;1225684;1224455;5552267;5554488;2225544;9999955 | 28/04/2006 10:35:11 | 28/04/2006 11:58:07

The 7 digit numbers in the JobTask field, each one separated by a semi-colon, represent 7 jobs that all started at the same StartTime and all finished at the same EndTime. The sequence of numbers is always preceded by the MPK; value.

What I want to do is run a Make Table query to get the above to look like this:

JobTask | StartTime | EndTime | IsMpk
1225684 28/04/2006 10:35:11 28/04/2006 11:58:07 Yes
1224455 28/04/2006 10:35:11 28/04/2006 11:58:07 Yes
5552267 28/04/2006 10:35:11 28/04/2006 11:58:07 Yes
5554488 28/04/2006 10:35:11 28/04/2006 11:58:07 Yes
9999955 28/04/2006 10:35:11 28/04/2006 11:58:07 Yes

Any help with this conundrum, as usual, gratefully received!

Thank you,

--
Steven

 

[Remou]

I think you will need to use VBA. Something like this "Split Comma Delimited Cells Across Rows: thread181-1336188", perhaps.

 

[proximity]

Thanks Remou,

Your suggestion was just the ticket!


--
Steven