Split two data elements that are in one field

  • Thread starter Thread starter Lisa W.
  • Start date Start date
L

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.
 
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?
 
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);
 
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,

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

Thanks,
Lisa W.

Jerry Whittle said:
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.


Jerry Whittle said:
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);
Click to expand...
Click to expand...
 
Back
Top