Pulling non-normalized data and inserting new row

  • Thread starter Thread starter Goody
  • Start date Start date
G

Goody

Hard to explain in the Subject line...

I have a table that in one column there are multiple
items that SHOULD be normalized into their own rows; I'd
like to do that using a query.

For example, I have a column (applications) that has
multiple items (presently separated by a ^). In order to
be properly normalized, each instance of those items that
are now sitting in the same field of the same record,
should be in their own separate rows.

What I think I need to do is parse the column and if an
instance of the ^ is found, the string should be parsed
and a newly created row should be inserted.

Is there a query that will pull out the concatenated data
and put them into their own rows, or should I look at
looping the data in a VB app?

Sounds like the opposite of concatenation, doesn't it...

TIA

Goody
 
Is there a query that will pull out the concatenated data
and put them into their own rows, or should I look at
looping the data in a VB app?
Click to expand...

You don't need VB - Access comes with Visual Basic for Applications
builtin.
Sounds like the opposite of concatenation, doesn't it...
Click to expand...

Yep... and a good thing too.

Two suggestions, one involving no VBA but a fair bit of tedium, the
other a bit more elegant using VBA:

1. Create an Append query appending

Left([fieldname], InStr([Fieldname], "^") - 1)

using a criterion of

LIKE "*^*"

followed by an Update query with the same criterion, updating your
field to

Mid([Fieldname], InStr([Fieldname], "^") + 1)

Then keep running these until there aren't any records.

2. Use a VBA function using the Split() function to split the string
into an array, followed by adding records using a Recordset.
 
Thanks John, just got a chance to work on this again. I'd
like to try the append query solution you suggested.

I'm probably doing something wrong, keep
getting 'property not found' error when I try and run the
SQL. Here's what I've entered:

INSERT INTO Ford2
SELECT left(application, instr(application, "^") -1)
FROM Ford1;

Ford2 is an empty table (although I certainly can put it
right into the same table), Ford1 is the full source
table.

Am I close with the SQL?

Thanks a bunch!

Goody
-----Original Message-----
Is there a query that will pull out the concatenated data
and put them into their own rows, or should I look at
looping the data in a VB app?
Click to expand...

You don't need VB - Access comes with Visual Basic for Applications
builtin.

Sounds like the opposite of concatenation, doesn't it...
Click to expand...

Yep... and a good thing too.

Two suggestions, one involving no VBA but a fair bit of tedium, the
other a bit more elegant using VBA:

1. Create an Append query appending

Left([fieldname], InStr([Fieldname], "^") - 1)

using a criterion of

LIKE "*^*"

followed by an Update query with the same criterion, updating your
field to

Mid([Fieldname], InStr([Fieldname], "^") + 1)

Then keep running these until there aren't any records.

2. Use a VBA function using the Split() function to split the string
into an array, followed by adding records using a Recordset.



.
Click to expand...
 
Thanks John, just got a chance to work on this again. I'd
like to try the append query solution you suggested.

I'm probably doing something wrong, keep
getting 'property not found' error when I try and run the
SQL. Here's what I've entered:

INSERT INTO Ford2
SELECT left(application, instr(application, "^") -1)
FROM Ford1;
Click to expand...

I'm guessing it thinks you're referring to the Application VBA object
rather than to the field named application. Try putting the fieldname
in square brackets, or changing the fieldname to avoid using a name
with other meanings!
 
Only if you use [Application] instead of application...
and maybe not
even then. Did you? Would you consider changing the fieldname? If you
put the expression in the Immediate window after typing Ctrl-G (e.g.

?Left(InStr("somefield^anotherfield", "^"))

what do you get?
Click to expand...

I did try using brackets in the SQL statement (inside
Access query), no luck.

Some success when I use this statement:

? left(rsdata("application"),(instr(rsdata
("application"), "^"))-1)

I think I'm finally getting to the point where I can use
one of your early suggestions ... pull out data using the
above statement, then do an update query to replace the
field with everything EXCEPT that first bit of data
pulled out. Then loop the data over and over until it's
all cleaned up.

I'll work on that now that I see it working in the
immediate window. Thanks a TON for your help, John.
 
Back
Top