If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Split two data elements that are in one field
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. |
#2
|
|||
|
|||
Split two data elements that are in one field
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, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Lisa W." wrote: 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. |
#3
|
|||
|
|||
Split two data elements that are in one field
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, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#4
|
|||
|
|||
Split two data elements that are in one field
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" wrote: 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, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#5
|
|||
|
|||
Split two data elements that are in one field
Jerry,
I'll try your second suggestion and let you know if I get it to work. Thanks, Lisa W. "Jerry Whittle" wrote: 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" wrote: 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, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
Thread Tools | |
Display Modes | |
|
|