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!

 

[Norie]

Did you see my post with some sample code?

I think it would work if you replaced ErrorCode with RejectCode.

I'm still unclear which field is user though.

 

[Eklypz]

Sorry for being unclear. I am going to try the code now.

The user is Sales Rep.

I would like the 11 non Reject Code fields to have 1 reject. So it would be 1 to many relationship I believe. (11 fields to 1 reject)

 

[Eklypz]

I have not done much with VBA unfortunately, so I am sure I am doing something terribly obvious wrong. But, I keep getting an error, "User-defined type not defined" for the row headed by Dim.

here is what I modified from your code sample:

Sub test()
Dim db As test
Dim rstDest As Recordset
Dim rstSource As Recordset
Dim I As Integer
    Set db = CurrentDb
    
    Set rstSource = db.OpenRecordset("SELECT SalesRep, RejectCode1, RejectCode2, RejectCode3, RejectCode4, RejectCode5 FROM SalesRep")
    Set rstDest = db.OpenRecordset("SELECT SalesRep, ErrorCode From UserErrors")
    
    rstSource.MoveFirst
    
    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
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    Set db = Nothing
End Sub

 

[Norie]

Dim db As test

That should be Dim db As Database.

And you will need to set a reference to the Microsoft DAO libary.

To do that, in the VBA editor goto Tools>Reference and scroll down to Microsoft DAO. There will be various versions there, check the one with the highest number.

For me that was Microsoft DAO 3.6 Object Library.

Sorry that was my fault - I use this sort of thing all the time and forgot that it is not selected by default.

 

[Eklypz]

My reference is greyed out...do I need to download a SDK or something?

 

[Norie]

My reference is greyed out

What do you mean?

EDIT

BTW

For I = 1 To 4

should be

For I = 1 To 5

Again my mistake - hard to do these things without having stuff to test on.

 

[Eklypz]

I cannot select it since it is greyed out. It does not have the black text that denotes an active option to select so cannot choose it.

 

[Norie]

Are all the Microsoft DAO libaries unavailable?

If so you might have to go down a different route involving append queries.

 

[Eklypz]

Yes, they are all unavailable and I just talked to my IT guys and they are all SQL people and have no idea how to add those to Access

Append queries sound interesting though, someone else had mentioned that I think.

 

[Norie]

Try this

Sub test()
Dim db
Dim rstDest
Dim rstSource
Dim I As Integer
    Set db = CurrentDb
    
    Set rstSource = db.OpenRecordset("SELECT User, ErrorCode1, ErrorCode2, ErrorCode3, ErrorCode4, ErrorCode5 FROM Users")
    Set rstDest = db.OpenRecordset("SELECT User, ErrorCode From UserErrors")
    
    rstSource.MoveFirst
    
    While Not (rstSource.EOF)
    
        With rstDest
            
            For I = 1 To 5
                .AddNew
                .Fields("User") = rstSource.Fields(0)
                .Fields("ErrorCode") = rstSource.Fields(I)
                .Update
            Next
            rstSource.MoveNext
        End With
    Wend
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    Set db = Nothing
End Sub

Replace the field names to match your own.

BTW

I really mucked up on the previous code, it would have sent you into an endless loop without the rstSource.MoveNext line.

I've tested it on a small table and it seems to work.

You will notice that I have removed the type declarations for the various variables db, rstSource etc. Normaly it is a good thing to do this but you can get away without it.

BBTW

I am now praying it will work and restore any sort of reputation I might have had on the board.

Fingers crossed