I have an access database I need data formatted from.

[William.Kalies]

I have an access database I need data formatted from. There


is a column in the database with multiple strings concatenated together, I
need them split up and then appended to a new row with the values from the
row.


For example, the row may read Zztest,Joshua 01/01/1970
00001000020000300004. I need the 00001 broke out into a unique row with the
preceding values, the value in that column will always have the same number
of characters. That row need to be exported as:
Zztest,Joshua 01/01/1970 00001
Zztest,Joshua 01/01/1970 00002
Zztest,Joshua 01/01/1970 00003
Zztest,Joshua 01/01/1970 00004

Any help would be greatly appreciated.

Thanks,
Will

[ranman256]

use EXTERNAL DATA, IMPORT TEXT, FIXED WIDTH

use cursor to mark the break boints of the various fields.

[William.Kalies]

I think that would work except for the last field which has the groups of numbers (groups of four). Sometimes the last field will just contain the four numbers, other times eight or twelve numbers that need to be broken into groups of four. I will definitely give your suggestion a shot. Thanks.

[William.Kalies]

That didn't work Mainly because I need the information copied from the first couple fields and then inserted into new records breaking up the last field which is broken into groups of four.

[ssanfu]

Its not clear where the data is coming from: an external file or a table in the current dB. Do you want the the parsed data (Zztest,Joshua 01/01/1970 00001) to go into the same table or a different table or written to a a text file or .....???

In any case, it sounds like you probably have to use VBA to get the results you require/desire.
In vastly general terms, it is:
1) Read a line/record
2) Parse the data
3) Write a line/record.

Is this a one time job?

[William.Kalies]

Sorry, I am just starting to use forums so sometimes my questions may seem very vague. The data is coming from 60 tables (all formatted the same) within an access database. The information should go into a new table in the same database. This is a one-time job of migrating old data into a new table and splitting up the last field into groups of four (the last field contains a collection of numbers such as 000100020003000400050008, however the amount of number is different for every record). Thank you in advance for any assistance.

[ssanfu]

How are your skills programming with VBA?

[William.Kalies]

Intermediate, basically if I have a starting point and some pointers along the way I should be able to get this working. I worked for the Dept. of Corrections building and coding Access databases for almost ten years before starting at my current job. Just a little rusty on the coding aspect.

[ssanfu]

I would create a new table the would hold the names of the 60 tables with the data you want to parse.

Does the data look like
Zztest,Joshua 01/01/1970
00001000020000300004

or like
Zztest,Joshua 01/01/197000001000020000300004 ??


Is the data in 1 field or 2?

[William.Kalies]

It is in three fields, the name, the DOB, and then the collection of numbers I need to split up. I think I have a rough sketch of what needs to happen, and I'll be creating a new table. After having the process stew around in my head some I think I've got it. Thanks for the pointers.

[ssanfu]

I don't know your field names or the structure of the tables, but I threw together an example.
"Table1" and "Table2" are data source tables
"tblTables" is the table that has the source tables with data to parse
"Results" is the new table of parsed data.....

Since you are already parsing the data, I would change the structure of the new table to have "LastName" and "FirstName" fields (be aware "Name" is a reserved word in Access and shouldn't be used for object names).
It is easier to split the names now rather than try and split them later.



Hope it helps...



Oops, just saw you last post......

[William.Kalies]

Thank you very much. I was in the middle of writing some of that code so it saved me some work. I appreciate your help on this.

[ssanfu]

Glad to help....