Need to transform data from one field into multiple records 1

[Elysynn]

Hello,

Wow. I have an interesting problem, and I'm not sure where to start. I am querying from an SQL database from Access 2003, and I have 2 fields that I am concerned about: Server and Values. The problem here isn't this existing query, it's what to do with the data once I have it. Rather than having one record for each value in the server, there is one record for the server with the values all in one field separated by a #; So it looks something like this:

|ServerName|Value
 Server1A  |12345#;23456#;34567-34590#;45678
 Server2A  |56789#;78901

What I would like to do is get something that looks like this:

|ServerName|Value
 Server1A  |12345
 Server1A  |23456
 Server1A  |34567-34590
 Server1A  |45678
 Server2A  |56789
 Server2A  |78901

I'm not sure if this is the right area to be posting this - but as I said, i'm not sure where to start. I've not seen something like this before. I was't even sure how to search for this... Any assistance is appreciated.

TIA,
Elysynn

 

[Remou]

I think I would use Split and two recordsets for this.
- Open Recordset 1 for the above data
- For each record Split Value
- For each item in the array, append a new record to Recordset 2.

 

[Golom]

... or look at faq701-6293

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Elysynn]

Golom - thank you for pointing me to the FAQ. Just goes to show what happens when your vocabulary fails you... I've been sidetracked on the issue I'm currently dealing with, I just wanted to let you know I've found it helpful. I've got some tweaking and understanding to do yet - but I appreciate it.

-Elysynn