Data Conversion Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shai@shaiguy.com - 30 Jul 2007 22:26 GMT

I'm generally pretty good at this, but can't seem to come up with the
best way to do this "One Time" task. I have a 2-column table in
Access, sampled below with some sample data shown:

TenantID, OperatingStates
"123", "FL(3), AK(10), NV(4)"

I would like to convert this data into a 3-column table to look like
this:

TenantID, OperatingState, NumberofLocs
123, FL, 3
123, AK, 10
123, NV, 4

Hence the one record from the Origin table, will be converted to 3
records on the Destination table, such that the TenantID and
OperatingState fields together, will make up the primary key.

Any thoughts of an append query or some funky formula concoction in
Excel to make this one-time conversion happen ?

Please email as well as post....

Best
Shai (nyshai@yahoo.com)

Reply to this Message

John Welch - 30 Jul 2007 23:30 GMT

Shai, try this:
SELECT Table1.tenantid, Left([operatingstates],2) AS OperatingState,
Replace(Replace(Mid([operatingstates],3),")",""),"(","") AS NumberofLocs
INTO table2 FROM Table1;

-John

> I'm generally pretty good at this, but can't seem to come up with the
> best way to do this "One Time" task. I have a 2-column table in
[quoted text clipped - 22 lines]
> Best
> Shai (nyshai@yahoo.com)

Reply to this Message