HELP!! Convert Column to row

[dj982020]

I have imported an Excel worksheet to Access, and the formatting is really bad. I need a query that converts a table that looks like this:

FIELD1
ID
1234
NAME
John Doe
PHONE
555-1212

To a table that looks like this:

ID NAME PHONE
1234 John Doe 555-1212

 

[dhookom]

Are there always the same number of "rows" per "record"? Is the data consistently an "odd numbered" row that has a column heading followed by an "even numbered" row that has a data value?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dj982020]

Yes, there are always the same number of "rows" per "record". There are random empty rows so there is no consistency as far as odd or even number rows containing data. There is, however, an Autonumber assigned to each row (i.e. every name field has ID=6).

 

[MichaelRed]

Why not do the xpose in Excel (which has a built in function for that) and then do the import?




MichaelRed

 

[JonFer]

You can use sorting and various formulas in Excel to convert to:

ID     1111  1112
Name    Joe  Mary
Phone  5455  4545

Then use the paste/transpose option as MichaelRed suggests.



John

http://www.interautomateconsulting.com