Split Data from a Table Column into Rows

Question

Hello

Is there a method in MS Access to split data from a table column into rows? I have a table with a column of data that I need to be separated into different rows while keeping the data in the other columns the same.

Here's what my table look like:

Number#                 City                      State                        Zip

-------------           ----------                ----------                 ----------

1                        Brownwood                TX                    76803, 76804  

2                        Valera                        TX                    76884, 76888 

3                        Chesapeake                OH                   45619

I need to split the Zip column by the comma/spaces and maintain the order in which they appear. I'm trying to get the data in the table that looks like this:

Number#                 City                      State                        Zip

-------------           ----------                ----------                 ----------

1                        Brownwood                TX                    76803

1                        Brownwood                TX                    76804  

2                        Valera                        TX                    76884 

2                        Valera                        TX                    76888 

3                        Chesapeake                OH                   45619

I know this could be done easily in Excel however when it comes to Access, I'm a novice.

Any advice would be appreciated.

Answer 1

This was exactly what I was looking for. Thanks so much everyone!

Answer 2

If you are interested here is another way using VBA to update and insert your existing table. Be sure to make a copy just in case it blows something up. Change the field and table names to suit your database.

Public Function SplitZips()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String

    Dim aryZips As Variant

    Dim x As Integer

    Dim lngNum As Long

    Dim strCity As String

    Dim strState As String

    Dim strZip As String

    Set db = CurrentDb

    strSQL = "SELECT [Number#], City, State, Zip " _

        & "FROM ZipCodes " _

        & "WHERE InStr(Zip,',')>0"

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    With rs

        Do While Not .EOF

            aryZips = Split(!Zip, ",")

            For x = 1 To UBound(aryZips)

                lngNum = ![Number#]

                strCity = !City

                strState = !State

                strZip = aryZips(x)

                .AddNew

                ![Number#] = lngNum

                !City = strCity

                !State = strState

                !Zip = strZip

                .Update

            Next

            'Remove all zips but first one

            .Edit

            !Zip = Left(!Zip, InStr(!Zip, ",") - 1)

            .Update

            .MoveNext

        Loop

    End With

    Set rs = Nothing

    Set db = Nothing

End Function

Answer 3

The Split function, which returns an array, can be used to parse a delimited string expression:

Call it in a UNION ALL query like this:

SELECT [Number#], City, State,

Split([YourTable].Zip,",") (0) AS Zip

FROM [YourTable]

UNION ALL

SELECT [Number#], City, State,

Trim(Split([YourTable].Zip,",")(1))

FROM [YourTable]

WHERE Instr([YourTable].Zip,",")>0

ORDER BY [Number#];

Answer 4

Use a union query that looks for comma(s) in the Zip field.  

Use Replace to count the number of commas.