Selected columns to rows

[DHKMDABB]

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.

[June7]

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 http://allenbrowne.com/ser-67.html#MultipleValues

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
SELECT Sheet1.ID
FROM Sheet1
GROUP BY Sheet1.ID
PIVOT Sheet1.Field1;

Use the query that joins the 3 crosstabs as source for inserting record into the final table.

[DHKMDABB]

June7 Thanks for your help crosstab qry. does the job.