MS Access, how do I organize a column into a row?

General Questions
#1

I’ve got a column of data similar to this:
1
3
4
5

I want the results to look like this:
1, 3, 4, 5

There’s got to be an easy way to do this in a query or report (I will eventually pull this data into a report).

What easy step am I missing?

Eli

#2

I don’t think that you’re missing anything. You’re trying do do one of those things that SQL doesn’t like to do. So bypass it. Create a Visual Basic function that opens the recordset, interates over it, and returns a string containing the concatenated values.

#3

Doing what you’re suggesting is essentially de-normalizing you data - not soemthing Access is going to want to do.

For a quick and easy way to accomplish it though you can always copy the info and “Paste Special” into Excel choosing the “Transpose” option.

But you should first ask yourself if you really want to do this.

#4

If you absolutely have to do it in SQL, you could join the table 5 times. But that would be wretched.

#5

Repeat after me 100 times: A database is not a spreadsheet. A database is not a spreadsheet. etc., etc.

Your best bet is to export it into Excel and transpose it there (Edit/Paste Special should do it automatically), but if this a meaningful conversion, I’m calling database abuse.

#6

Well, hold on here. You’re all assuming that the original data is correctly organized into normalized form. Maybe the OP is just trying to normalize it. I agree, the easiest way is to export the data to Excel, transpose it, and import it back, but ONLY if that’s what you REALLY want to do.

#7

You could add an autonumber to the table, then use it as the column headings in a crosstab query, Expr1:null as the row heading.

We’re talking about a report here, aren’t we? - a printed representation of the data, not a permanent change to its structure?
If so, I think we should lighten up a bit; there are valid cases where it is desirable to present multiple items of data as (say) a comma-separated list next to a master record that carries the matching key; particularly when the data is going to be embedded in a human-readable sentence.

#8

In addition to the quick and dirty excel method mentioned, you can also play around with the crosstab wizard in the new query dialog. Crosstabs are Access’ “pivot tables”.

#9

I agree with Mr. Ash, a simple Crosstab query will do that in a flash.

You’ll need at least three columns in the data with your data to use the wizard, which is the simplest way for a novice. For this example I’ve called the field with your actual data “CURRENT COLUMN DATA”. I’ve called the secondary field “CROSSTAB ROW PLACES” and filled the entire column with the number “1”, you can fill it with whatever you like but all fields need to be identical. . A third field called “CROSSTAB COLUM PLACES” was also created. Fill it with whatever column heading you want to correspond with you data.

Then go into queries wizard and make a New Crosstab using “CROSSTAB ROW PLACES” as the row headings, “CROSSTAB COLUM PLACES” as the column heading and the “First” function of “CURRENT COLUMN DATA” as the intersection value. Then after the table is created open the design view and delete the column with “Totalof***”.

Or you can open a new SQL and paste in the following and it should work.



TRANSFORM First(Table1.[CURRENT COLUMN DATA]) AS [FirstOfCURRENT COLUMN DATA]
SELECT Table1.[CROSSTAB ROW PLACES]
FROM Table1
GROUP BY Table1.[CROSSTAB ROW PLACES]
PIVOT Table1.[CROSSTAB COLUM PLACES];

Works for me. Gets exactly the result asked for.

#10

We are talking about a report. The data is normalized, but the same piece of data may be assigned to different categories. Rather than a row for each category, we want to transpose the data to a single row on the report.

De-normalizing the data is not a good option. Part of the reason I’m doing this is the last person de-normalized the data, and corrupted it so thoroughly that it no longer functioned intelligently.

Eli

#11

Correction:

“we want to transpose the data to a single row on the report.”

should read

“we want to transpose the categories to a single row on the report.”

I’ll play with the crosstab function. Perhaps using that in conjuction with the report format I need will work.

Thanks!

Eli