Selected columns to rows?

General discussion on MS Access and its databases.

Selected columns to rows?

Postby Gregory A. Chase » Tue May 06, 2014 9:59 am

I need your help, I have extraction report from calculation program and don’t have access to tables. When I import it to MS Access it looks like attached Sheet1. Is there a simple SQL query or VBA code which will allow me to import the data to new Sheet2 table? Fields 1,7&13 will always be the same and I want them to be column names, Fields 4, 10 &14 contains data which will be different each time I delete the old calculation from Sheet1 and import a new one. I would like to be done in Access as I have to import few hundred calculations and store it in Sheet2 table.
Gregory A. Chase
Posts: 22
Joined: Mon Mar 24, 2014 6:14 am

Re: Selected columns to rows?

Postby SteveJ » Mon May 12, 2014 6:07 am

Field2, Field3, Field5, Field6, Field8, Field9, Field11, Field12, Field16 can be discarded?

Field14 does not have data, Field15 has data.

If I understand correctly, result from this example will be 51 fields and one record.

You need to pivot 3 sets of data. This is a CROSSTAB query. CROSSTAB requires 3 fields. I think will need another field with a common value on each row. Each import will need this field populated, number 1 will serve. Can use the ID field for this. An UPDATE query can populate the field. This will be RowHeading field.

For one method of CROSSTAB for multiple sets of values review

Another approach is to do 3 separate CROSSTAB queries then join them on the ID field.

Here is one of the CROSSTAB queries:
TRANSFORM First(Sheet1.Field4) AS FirstOfField4
FROM Sheet1
PIVOT Sheet1.Field1;

Use the query that joins the 3 crosstabs as source for inserting record into the final table.
Posts: 20
Joined: Tue Mar 25, 2014 6:31 am

Return to MS Access- Help Forum

Who is online

Users browsing this forum: No registered users and 1 guest