comining same fields into 1 new column?

[Eklypz]

Hi, I have a legacy excel spreadsheet that we use for analysis. The format of the spreadsheet is unable to be changed and I import it into access to do number crunching on. The problem I have is that each entry can have multiple error codes which are listed in different columns on the original spreadsheet. It is something like this:


User | Error Code | Error Code | Error Code | Error Code | Error Code |

I would like to combine all the error codes into one column to have better data and easier manipulation with the error codes. So I can do things like count the particular error code per user, etc.

Is there a good way to go about creating a new table from my existing table to have only 1 user and 1 error code per line? (including all 5 error columns to be per user).

Thanks!

 

[mdmilner]

Has anybody else figured out a working answer for a problem only to realize they'd failed to notice the other two pages of a thread? (and that it'd already been answered?)

I do have a recommendation about field referencing though.

Going back to the basic form of Nories very first response (looks like it works to me)

    While Not (rstSource.EOF) 
    
        With rstDest 
            .AddNew 
            For I = 1 To 4 
                .Fields("User") = rstSource.Fields(0) 
                .Fields("ErrorCode") = rstSource.Fields(I) 
                .Update 
            Next 
        End With 
    Wend

Substitute this for .Fields("User") & .Fields("ErrorCode")

.Fields(0).Value
.Fields(1).Value

The point is, removing the field name makes the code universal and no longer tied/specific to a single table and it's field names.

Mike