Turning Rows into Columns

[mary10k]

I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns. My data set contains about 1,000 rows, so I cannot simply paste special in Excel.

Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567

Any solutions would be greatly appreciated. I have tried Pivot and Transform, neither of which seems to work.

Thank you.

 

[maw230]

I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns. My data set contains about 1,000 rows, so I cannot simply paste special in Excel.

Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567

Any solutions would be greatly appreciated. I have tried Pivot and Transform, neither of which seems to work.

Thank you.

I'm having trouble visualizing how this looks in table form...Did you try a Crosstab Query in Access?

 

[mary10k]

The data looks like this:

Field 1 Field 2
Patient Id 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567

Basically, I am trying to pivot everything from Field1 into separate columns and insert Field 2 into each corresponding column.
Thank you

 

[jdraw]

Do these 5 fields make up a record?
Are all records consistent, that is all have these 5 fields?

 

[mary10k]

Yes, all 5 fields make a record. I have about 100+ repeating records in the file consisting of the same fields. They are consistent as well.

 

[jdraw]

Here is vba code to do the conversion to records in a table.

You will need a table RealRecs as shown in the attached jpg to hold the final data.
I called the input table with the 5 records ---RecordsFive. It has the design structure shown in RecordsRaw.jpg FieldRaw is 80 chars.

You can adjust table and field names as you wish, but the code works with these tables.

'---------------------------------------------------------------------------------------
' Procedure : convertRowToCols
' Author    : mellon
' Date      : 21/01/2015
' Purpose   : To convert data identified in
'http://www.access-programmers.co.uk/forums/showthread.php?t=273818
'into records usable by Access.
'This sub procedure reads a record of the incoming data and determines the record sequence
' and based on findings places the data value into the appropriate field in the record.
'
'---------------------------------------------------------------------------------------
'
Sub convertRowToCols()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rt As DAO.Recordset
    Dim r1 As String 'record type 1
    Dim r2 As String 'record type 2
    Dim r3 As String 'record type 3
    Dim r4 As String 'record type 4
    Dim r5 As String 'record type 5
    
   On Error GoTo convertRowToCols_Error

    r1 = "Patient Id"    '10
    r2 = "Patient Name"  '12
    r3 = "Send Reason"   '11
    r4 = "Provider Name" '13
    r5 = "Visit Number"  '12
    
    'these variables hold the related data by record type.
    Dim r1d As String
    Dim r2d As String
    Dim r3d As String
    Dim r4d As String
    Dim r5d As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("recordsFive")
    Set rt = db.OpenRecordset("realRecs")
    Do While Not rs.EOF
        If Left(rs!fieldraw, 10) = r1 Then
            'r1
            r1d = Trim(Mid(rs!fieldraw, 11))
        ElseIf Left(rs!fieldraw, 12) = r2 Then
            'r2
            r2d = Trim(Mid(rs!fieldraw, 13))
        ElseIf Left(rs!fieldraw, 11) = r3 Then
            'r3
            r3d = Trim(Mid(rs!fieldraw, 12))
        ElseIf Left(rs!fieldraw, 13) = r4 Then
            'r4
            r4d = Trim(Mid(rs!fieldraw, 14))
        ElseIf Left(rs!fieldraw, 12) = r5 Then
            'r5
            r5d = Trim(Mid(rs!fieldraw, 13))
            
            'we're finished with the prerequisites so write a record to RealRecs table
            
            rt.AddNew
            rt.Fields(1) = r1d
            rt.Fields(2) = r2d
            rt.Fields(3) = r3d
            rt.Fields(4) = r4d
            rt.Fields(5) = r5d
            rt.Update
          
        Else
            MsgBox "unrecognized record type"
        End If
        rs.MoveNext
        
    Loop

   On Error GoTo 0
   Exit Sub

convertRowToCols_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure convertRowToCols of Module AWF_Related"

End Sub

This was the input
ID	FieldRaw
1	Patient Id 111111
2	Patient Name Doe, Jane
3	Send Reason Cannot resolve provider
4	Provider Name Doe, John
5	Visit Number ABCD#F1234567

and this is the output

RecID	PatientID	PatientName	SendReason	ProviderName	Visitnumber
   1  	111111	Doe, Jane	Cannot resolve provider	Doe, John	ABCD#F1234567

Good luck.

 

[mary10k]

JDRAW - thank you! This is brilliant. I've added your code into a Module and created the tables as you have outlined above. Could you please tell me how I would call the Module or run it?

I loaded data into the table, but nothing is in the output table.

Thank you.

 

[jdraw]

You could build a form with a button.
On the Click event of the button, you would have code to run the subprocedure.
(There is a wizard in form builder to help you)

Or
You could go to the Module containing the subprocedure.
Click your mouse anywhere in the subprocedure code, then Click on the green triangle in the menu, Or Click Run above it.

You really should learn a little vba. It is extremely handy and helpful.
See the debugging link in my signature


I have added some comments to the code. Can you follow some of what the code is doing?

 

[mary10k]

Thank you again. I was heading down the path of a macro, but couldn't quite figure it out.

I am getting an error that states 'unrecognized record type'. My table setup is as you outlined in your .jpgs 'text and auto number'. If I click on the 'ok' button in the error message, the code does parse, but I have thousands of records, so I can't click on the error that many of times. I've googled that error message and nothing comes up. Do you know how to resolve it?

I am going to search for a VBA book soon to learn VBA.

Thank you.

 

[jdraw]

Thank you again. I was heading down the path of a macro, but couldn't quite figure it out.

I am getting an error that states 'unrecognized record type'. My table setup is as you outlined in your .jpgs 'text and auto number'. If I click on the 'ok' button in the error message, the code does parse, but I have thousands of records, so I can't click on the error that many of times. I've googled that error message and nothing comes up. Do you know how to resolve it?

I am going to search for a VBA book soon to learn VBA.

Thank you.

That error message should only arise when a record in the input does not match the format of the records you provided. If you look at the code, each If or ElseIf is related to record types r1--r5. It the incoming record does not match one of these ---The error message is displayed.

You may not find a book specific to vba. You often find Access... books of about 300 pages and little on vba.

There are many youtube videos and some online vba links.

FunctionX vba

Crystal's Learn vba

And there is techonthenet with many samples.


UPDATE: I just added a line to the code to display records in error.

 Else
            Debug.Print "**Bad record format  >" & rs!fieldraw '<--this line
            MsgBox "unrecognized record type"

Tested with new records
ID FieldRaw
1 Patient Id 111111
2 Patient Name Doe, Jane
3 Send Reason Cannot resolve provider
4 Provider Name Doe, John
5 Visit Number ABCD#F1234567
6 Patient Id 111125
7 This is not correct format
8 Patient Name Doe, Eliza
9 Send Reason Insufficient Info
10 Visit Number ABCD#F1234765


and it kicks out this record (as it should)

**Bad record format   >This is not correct format

 

[mary10k]

You're a hero! I had ID typed incorrectly. Fixed it and now it works. Thank you for the links and insight about learning VBA. I'll definately look at uTube.

Thanks again!

 

[jdraw]

Glad it is working. You should add that line of code. If you do have any bad records, it will show which ones they are.

Post questions anytime.
Good luck with vba.

 

[mary10k]

I created a form with a button to call the module and it does work, but the workflow is not appropriate for my process. I don't need the module until the very end of my process.

Could you or anyone please explain how I can make this into a function and call it through a macro if possible?

You could build a form with a button.
On the Click event of the button, you would have code to run the subprocedure.
(There is a wizard in form builder to help you)

Or
You could go to the Module containing the subprocedure.
Click your mouse anywhere in the subprocedure code, then Click on the green triangle in the menu, Or Click Run above it.

 

[jdraw]

We don't know your workflow.
However, if it is Access/system based, you could work from a mainform/switchboard.
Select whatever it is you do first, doit-- move to the next and repeat, and at the last step, invoke the button or selection that runs the subprocedure.