Split two data elements that are in one field

[Lisa W.]

I have a table that was created via an excel spreadsheet import. One of the
fields consist of two data elements, but I would like to split it so that the
data after the comma goes to a new row of data and pull in the additional
information as well.

Ex.
Site Code Addr1 Addr2 City State Zip Code
0Z39,0297 5757 Main St Suite 1 Balt MD 21228

I would like to move 0297 to a new row and pick the same address. Is this
possible?

Thanks in Advance,
Lisa W.

 

[Jerry Whittle]

Is the site code always the same number of characters and/or always split by
the comma?

Is this a one-time import or something that you will be doing frequently?

 

[Jerry Whittle]

Assuming the Site Codes are always the same number of characters, the SQL
statement below will create a new table LisaNew like you want. You'll need to
put in the proper table names.

SELECT * INTO LisaNew
FROM (SELECT Left([Site Code],4) AS TheSiteCode,
Lisa.Addr1, Lisa.Addr2, Lisa.City,
Lisa.State, Lisa.[Zip Code]
FROM Lisa
UNION
SELECT Mid([Site Code],6) AS TheSiteCode,
Lisa.Addr1, Lisa.Addr2, Lisa.City,
Lisa.State, Lisa.[Zip Code]
FROM Lisa);

 

[Jerry Whittle]

The more I think about it, the more I don't like putting the address data in
twice.

Rather you should keep the table the way it is now EXCEPT adding an
autonumber field called AddressID. Make that field the primary key.

Now create another table that splits the Site Codes and has a reference to
the original table. Something like below:

SELECT * INTO LisaSiteCodes
FROM (SELECT AddressID, Left([Site Code],4) AS TheSiteCode
FROM Lisa
UNION
SELECT AddressID, Mid([Site Code],6) AS TheSiteCode
FROM Lisa);

That way you can link the tables together to get the proper site codes for
each address.

 

[Lisa W.]

Jerry,

I'll try your second suggestion and let you know if I get it to work.

Thanks,
Lisa W.

The more I think about it, the more I don't like putting the address data in
twice.

Rather you should keep the table the way it is now EXCEPT adding an
autonumber field called AddressID. Make that field the primary key.

Now create another table that splits the Site Codes and has a reference to
the original table. Something like below:

SELECT * INTO LisaSiteCodes
FROM (SELECT AddressID, Left([Site Code],4) AS TheSiteCode
FROM Lisa
UNION
SELECT AddressID, Mid([Site Code],6) AS TheSiteCode
FROM Lisa);

That way you can link the tables together to get the proper site codes for
each address.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Assuming the Site Codes are always the same number of characters, the SQL
statement below will create a new table LisaNew like you want. You'll need to
put in the proper table names.

SELECT * INTO LisaNew
FROM (SELECT Left([Site Code],4) AS TheSiteCode,
Lisa.Addr1, Lisa.Addr2, Lisa.City,
Lisa.State, Lisa.[Zip Code]
FROM Lisa
UNION
SELECT Mid([Site Code],6) AS TheSiteCode,
Lisa.Addr1, Lisa.Addr2, Lisa.City,
Lisa.State, Lisa.[Zip Code]
FROM Lisa);