Rows to Columns

[rbm]

I'm sure that I am missing something fundamental, but I am stumped. I have a
table with rows of data like the following:

Set1, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
Set2, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
Set3, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
etc.

Each "set" has several attributes that I need to group in a report. The
report needs to be based on a query that formats that data like this:

Set1, N1a, N1b
Set1, N2a, N2b
Set1, N3a, N3b
Set1, N4a, N4b
Set2, N1a, N1b
Set2, N2a, N2b
Set2, N3a, N3b
Set2, N4a, N4b
Set3, N1a, N1b
Set3, N2a, N2b
Set3, N3a, N3b
Set3, N4a, N4b

Short of running a make-table query and using that table as the source for
the report, I can't figure it out. The reason I don't want to use
make-table/append queries is because of the overhead it uses (database bloat)
and because the data may change between running reports. Any ideas??

Thanks in advance.

 

[Michel Walsh]

SELECT set, N1a AS field1, N1b AS field2 FROM tableName
UNION ALL
SELECT set, N2a, N2b FROM tableName
UNION ALL
SELECT set, N3a, N3b FROM tableName
UNION ALL
SELECT set, N4a, N4b FROM tableName


as a saved query then base the report on that query. You can alias the
fields as it may fit, in the first SELECT clause or this union query.




Vanderghast, Access MVP

 

[Marshall Barton]

I'm sure that I am missing something fundamental, but I am stumped. I have a
table with rows of data like the following:

Set1, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
Set2, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
Set3, N1a, N1b, N2a, N2b, N3a, N3b, N4a, N4b, ...
etc.

Each "set" has several attributes that I need to group in a report. The
report needs to be based on a query that formats that data like this:

Set1, N1a, N1b
Set1, N2a, N2b
Set1, N3a, N3b
Set1, N4a, N4b
Set2, N1a, N1b
Set2, N2a, N2b
Set2, N3a, N3b
Set2, N4a, N4b
Set3, N1a, N1b
Set3, N2a, N2b
Set3, N3a, N3b
Set3, N4a, N4b

Short of running a make-table query and using that table as the source for
the report, I can't figure it out. The reason I don't want to use
make-table/append queries is because of the overhead it uses (database bloat)
and because the data may change between running reports. Any ideas??

The table should have been set up that way to start.
Noemalizing a spreadsheet like thing can be a mess.

In this case you can use a UNION query to get what you need:

SELECT Set, N1a, N1b
UNION ALL
SELECT Set, N2a, N2b
UNION ALL
SELECT Set, N3a, N3b
UNION ALL
. . .

 

[rbm]

Thanks!!! If I can't develop from the design view, I still struggle with
queries. This does the trick.

 

[rbm]

You are absolutely correct. The tables I am using are from my earlier
creative efforts and I like to think that I am much smarter now (not that
much apparently). Unfortunately, it is a little late for me to go back and
reinvent (i.e., normalize) my tables the way they should be. Thank you for
your response.