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!

 

[Andrew Fergus]

Hi, how many columns of error codes are there? Are there 5 per your example, are the more than 5, or is it in fact variable? Is there any other data in the Excel spreadsheet such as a date or any other reference data?

I'm thinking that this could be done with 5 similar append queries where each query appends one non-null error per user row into the new table - provided there are only 5 error columns then this is manageable. You could then list all 5 queries in one macro so that you only have to "press 1 button" as such.

There may be an easier way to do this with code, but VB is not my strength.

HTH, Andrew.

 

[Norie]

Can you explain further exactly what you import from Excel and what the resulting table in Access contains?

As Andrew has said this could be done with append queries or code but more information is definitely needed.

 

[Nuts]

If you are importing into one table with several fields, create a query using the import table as the column source. The only field you require then is:

<NewFieldName>:<ImportField1> & " " & <ImportField2> & " " & <ImportField3> etc...

That will return all the imported fields in one new field with spaces in between.

 

[Nuts]

The above reply is missing some syntax. If this works this time, use:

NewFieldName: ImportedFieldName1 & " " & ImportedField2 & " " & ImportedField3 & " " & etc...etc...

 

[Norie]

Nuts

As far as I can see this isn't actually what the OP wants.

He wants to convert something like this:

User1 | Error Code1 | Error Code2 | Error Code3 | Error Code4 | Error Code5 |

into

User1 Error Code1
User1 Error Code2
User1 Error Code3
User1 Error Code4
User1 Error Code5

 

[Eklypz]

To answer a few questions asked:

Norie is correct, I want it like that so I can have normalized data and count error codes per a lot of factors.

There are 5 errror code columns with 11 other columns

I import this from excel, the data is numbers, text, email addresses, dates, etc.


TYVM for the help. The & Solution will be good for one thing I want to do but I really think I need the data normalized to be able to do deeper analysis.

 

[Norie]

There are 5 errror code columns with 11 other columns

Can you please explain the data structure further?

What field name has the user and what field names do the error codes have?

 

[Norie]

Code could be something like this:

Sub test()
Dim db As Database
Dim rstDest As Recordset
Dim rstSource As Recordset
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
            .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

Assumptions

• Original table imported from Excel is called User
• It has fields User, ErrorCode1, ErrorCode2, ErrorCode3, ErrorCode4 and ErrorCode5
• There is a (blank) table called UserError with two fields User and ErrorCode

Obviously things will need to be changed according to your set up.

 

[Eklypz]

Can you please explain the data structure further?

What field name has the user and what field names do the error codes have?

Sure, the data structure is:

Field            Data Type
File                        Number
DBA                        Text
MID                        Number
TransmissionDate     Date
SalesRep                 Test
Manager                  Test
AISReceived             Date
AISComplete            Date
RejectDate               Date
RejectDetail             Memo
RejectCode1           Text
RejectCode2           Text
RejectCode3           Text
RejectCode4           Text
RejectCode5           Text
Assigned                 Text

Thanks for your help.