asked on

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

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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.

Gerhardpet

ASKER

@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?

IrogSinta

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"

Gerhardpet

ASKER

@IrogSinta
That did exactly what I wanted it! Thanks for your help...