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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.