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!

 

[Eklypz]

Ok, seems to compile now I am getting db errors though so am missing fields.

I do not understand where this is referring to:

Set rstDest = db.OpenRecordset("SELECT User, ErrorCode From UserErrors")

Are the ErrorCode and UserErrors in the new table I am creating? Should I go ahead and create that now with my field names?

You are absolutely stellar by me, thanks so much for taking so much time to help this newbie to advanced Access. ray:

 

[Norie]

Are the ErrorCode and UserErrors in the new table I am creating? Should I go ahead and create that now with my field names?

Check out the assumptions I made when I first posted the code.

BTW all these field names can be changed to suit your circumstances.

 

[Eklypz]

Re: combining same fields into 1 new column?

Your reputation is intact, it works! Sorry for the mindslip there about those fields And thanks once again very very much. You have helped fix a problem that has vexed me off and on for months!


I have 2 follow up questions with this:

1) If I want to add more fields to the new table for errors can I just put them inside the loop and reference them in the rstSource line?
2) There are records that are blank since not everyone has 5 errors, is there a way to not include the items that have no error code?

TYVM

 

[Norie]

1) If I want to add more fields to the new table for errors can I just put them inside the loop and reference them in the rstSource line?
2) There are records that are blank since not everyone has 5 errors, is there a way to not include the items that have no error code?

Yes to both.

Can you post the code that you are using and works and also say which fields you want in the new table?

Then we can alter the code.

 

[Eklypz]

here is the code:

Sub test()
Dim db
Dim rstDest
Dim rstSource
Dim I As Integer
    Set db = CurrentDb
    
    Set rstSource = db.OpenRecordset("SELECT SalesRep, RejectCode1, RejectCode2, RejectCode3, RejectCode4, RejectCode5 FROM Rejects")
    Set rstDest = db.OpenRecordset("SELECT SalesRep, RejectCode From OneReject")
    
    rstSource.MoveFirst
    
    While Not (rstSource.EOF)
    
        With rstDest
            
            For I = 1 To 5
                .AddNew
                .Fields("SalesRep") = rstSource.Fields(0)
                .Fields("RejectCode") = rstSource.Fields(I)
                .Update
            Next
            rstSource.MoveNext
        End With
    Wend
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    Set db = Nothing
End Sub

The fields I would like to add are File, MID, Manager.

If I need to can I make a relationship between this table and the original if I use the File as a primary key?

Another thought that would be nice if I could delete the records from the existing table before appending I know I am a feature creeper..

 

[Norie]

For the second question this might deal with the blank records:

Sub test()
Dim db
Dim rstDest
Dim rstSource
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
            
            For I = 1 To 5
                .AddNew
                If Not (IsNull(rstSource.Fields(I))) Then
                    .Fields("SalesRep") = rstSource.Fields(0)
                    .Fields("ErrorCode") = rstSource.Fields(I)
                    .Update
                End If
            Next
        End With
        rstSource.MoveNext
    Wend
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    Set db = Nothing
End Sub

Again I tried it with a small recreation of what I think your data is like and it seemed to work OK.

For the first you would need to alter the source recordset to include the other fields you want and change the For I=1 to 5 accordingly.

Something like this:

Set rstSource = db.OpenRecordset("SELECT File, MID, Manager, SalesRep, RejectCode1, RejectCode2, RejectCode3, RejectCode4, RejectCode5 FROM Rejects")

And

For I= 4 to 8

I hope you can work with that as I've maybe had a couple of already.

 

[Eklypz]

The blanks worked famously!

I am having an "item not found.." error, here is what I have for the code, the error refers to the "file" entry inside the loop.

here is what I have.

Sub test()
Dim db
Dim rstDest
Dim rstSource
Dim I As Integer
    Set db = CurrentDb
    
    Set rstSource = db.OpenRecordset("SELECT File, SalesRep,MID, Manager, RejectCode1, RejectCode2, RejectCode3, RejectCode4, RejectCode5 FROM Rejects")
    Set rstDest = db.OpenRecordset("SELECT SalesRep, RejectCode From OneReject")
    
    rstSource.MoveFirst
    
    While Not (rstSource.EOF)
    
        With rstDest
            
            For I = 4 To 8
                .AddNew
                If Not (IsNull(rstSource.Fields(I))) Then
                .Fields("File") = rstSource.Fields(0)
                .Fields("SalesRep") = rstSource.Fields(0)
                .Fields("MID") = rstSource.Fields(0)
                .Fields("Manager") = rstSource.Fields(0)
                .Fields("RejectCode") = rstSource.Fields(I)
                .Update
                End If
            Next
        End With
        rstSource.MoveNext
    Wend
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    Set db = Nothing
End Sub

Thanks again, truly appreciate the help!

 

[Norie]

I am having an "item not found.."

This is because there is no field in the rstDest.

Change

Set rstDest = db.OpenRecordset("SELECT SalesRep, RejectCode From OneReject")

to

Set rstDest = db.OpenRecordset("SELECT File, SalesRep,MID, Manager, FROM OneReject")

And also change

.Fields("File") = rstSource.Fields(0)
.Fields("SalesRep") = rstSource.Fields(0)
.Fields("MID") = rstSource.Fields(0)
.Fields("Manager") = rstSource.Fields(0)

to

.Fields("File") = rstSource.Fields(0)
.Fields("SalesRep") = rstSource.Fields(1)
.Fields("MID") = rstSource.Fields(2)
.Fields("Manager") = rstSource.Fields(3)

or to make it clearer

.Fields("File") = rstSource.Fields("File")
.Fields("SalesRep") = rstSource.Fields("SalesRep")
.Fields("MID") = rstSource.Fields("MID")
.Fields("Manager") = rstSource.Fields("Manager")

Hopefully that should sort it.

 

[Eklypz]

Yes it does, thanks again for the help. I really learned alot from this. Have a great weekend

 

[Nuts]

The simplest method to combine is to use a sql select statement like below:

SELECT tblName.User1,tblName.ErrorCode1 FROM tblName UNION
SELECT tblName.User1,tblName.ErrorCode2 FROM tblName UNION
SELECT tblName.User1,tblName.ErrorCode3 FROM tblName UNION
SELECT tblName.User1,tblName.ErrorCode4 FROM tblName UNION
SELECT tblName.User1,tblName.ErrorCode5 FROM tblName

the UNION lets you combine either tables, queries or Select statements and will not display any duplicates. If you want duplicates displayed, use UNION ALL.