Help with separating comma delimited data in one field 3

[th1011]

I have an exported table from a query that contains two fields (AppNumber and Folio). Each application can contain one or many folios, the problem is that the folio data has been entered using comma separators if more than one occurred (and there is no real limit to how many folio numbers there can be). They are both text fields and the following shows typical samples.

AppNumber Folio
05-0001 78912.0000
05-0002 12345.0000, 12345.0100, 45678.0000
05-0003 45612.0000

For the purposes of another application that will use this data I need to format the data like the following.

AppNumber Folio
05-0001 78912.0000
05-0002 12345.0000
05-0002 12345.0100
05-0002 45678.0000
05-0003 45612.0000

Thanks in advance for any help you can offer.

 

[MrBillSC]

A not very elegant solution, but the only one I can come up with would involve multiple queries. The first outputs your records to a table that includes only the first folio (use an expression Left([folio],10)). Then use append queries to add the remaining folios (Mid([folio],13,10)) etc. You will need to run a query for the largest number of folios contained in a single record.

Hope this helps,
MrBill

 

[Blorf]

An alternate would be a function that opens your data as a record set and parses it one at a time. Each time adding a record to your local table.

ChaZ


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Golom]

Have a look at thread701-943082