Converting Rows to Columns - HELP!

[jeanna143]

I have a table that is a repetitive list of data. Instead of having a column with rows of data underneath we have Column A listing over and over what should be the column headings and then the corresponding data is in Column C.

For Example:
Name John Doe
Address 1234 street
***
Name Jane Doe
Address 1235 Street
***

What I need to do is Make the table look like this:
Name Address
John Doe 1234 Street
Jane Doe 1235 Street

I tried using a CrossTab query and a Pivot table but they only will return either a count or the first or last row, etc. How can I convert this table and return all rows?

Thank you!!
Jeanna

 

[Golom]

I need some more info. You said that your table structure is like this:

Field1 Field3
Name John Doe
Address 1234 Street
Name Jane Doe
Address 1235 Street

Seems to me that if each line in the above is a record in the table then sorting the table would destroy the relationship because "John Doe's" address is in the record following his name and any sort that you do will destroy that relationship. How do you link an address to the person? Is that what the missing "Field2" is for?

Note that SQL does not guarantee any particular ordering of records in a result set if you don't use an Order By clause.

 

[jeanna143]

Hello Golom,

I do not want to sort the spreadsheet. I want to convert it so that the rows such as Name, Address, etc go across the top as column headers and the data falls under it.

Utilizing a crosstab query I can capture the format that I need, however, it wants to just return the count of records rather than the list of them. Or I can change it to return the first or last record correctly but not the full range of records.

What I want is to convert the table so that it behaves like a normal spreadsheet with column headings and rows of data. The record sets are separated by ***.

Thanks,
Jeanna

 

[Golom]

Yes. I understand what you want to do.

The issue is that, to do it, it is necessary to select different records in your current table to build one row in the new table. My question was directed at finding out how you determine that "1234 Street" is "John Doe's" address. Is it just by the fact that its the next record in sequence or is there some other way to make that association?

Assuming that "... next record in sequence ..." is the answer, place an AutoIncrement field (call it 'AutoNum' for discussion purposes) somewhere in the current table then we could make the association with some SQL like:

Select A.Field3 As [Name],
(Select X.Field3 From tbl As X Where X.AutoNum = A.AutoNum + 1) As [Address]
From tbl As A
Where A.Field1 = "Name"

Wrap that inside a "Select Into" statement like this

SELECT INTO NewTable
FROM
(Select A.Field3 As [Name],
(Select X.Field3 From tbl As X Where x.AutoNum = A.AutoNum + 1) As [Address]
From tbl As A
Where A.Field1 = "Name&quot


and you're done.