Convert row to column in Acsess

I'm exporting from a Pervasive database to a PostgreSQL from one ERP to another ERP system using an Access database. I'm linking to both databases using ODBC.

In the attached Access database I have a table from the Pervasive database that contains one row of data for the Fiscal Calendar/Periods.

I want to convert the table rows to 2 columns, the field name should be a column and the field data should be another column.

I also want to do the same for the query "fiscal periods in roman calendar"

Is this possible?
ee-database.accdb
LVL 1
GerhardpetAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
IrogSintaConnect With a Mentor Commented:
Here's one way:
Create a table called CalendarPeriod with a Text field called Period and a Number field called PeriodDate.
Create a module, add the following code and run it.
Private Sub GrabFromTable()
    Dim rsSrc As Recordset
    Dim rsTgt As Recordset
    
    Set rsTgt = CurrentDb.OpenRecordset("CalendarPeriods")
    Set rsSrc = CurrentDb.OpenRecordset("CTRL_PERIODS")
    
    'iterate from the 3rd to the last field in CTRL_PERIODS and append to target table
    For i = 2 To rsSrc.Fields.Count - 1
        
        rsTgt.AddNew
        rsTgt!Period = rsSrc.Fields(i).Name
        rsTgt!PeriodDate = rsSrc.Fields(i)
        rsTgt.Update
    
    Next
    
    rsTgt.Close
    rsSrc.Close
    Set rsTgt = Nothing
    Set rsSrc = Nothing
    
End Sub

Open in new window

You can do something similar for the query.

Ron
0
 
Dale FyeCommented:
on my iPad, so I cannot open your file.

A normalizing query would normally look something like:

SELECT "Column1" as Description, [Column1] as ColValue
FROM yourTable WHERE [Column1] IS NOT NULL
UNION ALL
SELECT "Column2" as Description, [Column2] as ColValue
FROM yourTable WHERE [Column2] IS NOT NULL

This, of course assumes that the actual Values in [Column1] and [Column2] are of the same data type.
0
 
GerhardpetAuthor Commented:
@IrogSinta
That worked perfectly..

The only thing left is to run the module from a form/button and also a confirmation message that is done running.

Can you help with that?
0
 
IrogSintaCommented:
Well then just insert that code in the OnClick event of a button that you place on a form.  At the end of the code, just simply add a message box like so:

MsgBox "Done"
0
 
GerhardpetAuthor Commented:
@IrogSinta
That did exactly what I wanted it! Thanks for your help...
0
All Courses

From novice to tech pro — start learning today.