View a table Vertically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has Queue Numbers with settings for each QueueNumber

Presently the data is stored like this:
QueueNum 1 Setting1 Setting2 Seting 3
QueueNum 2 Setting1 Setting2 Seting 3
QueueNum 3 Setting1 Setting2 Seting 3
QueueNum 4 Setting1 Setting2 Seting 3

I'd like to view it like this:

QueueNum1 QueuNum2 QueueNum3 QueueNum4
Setting1 Setting1 Setting1 Setting1
Setting2 Setting2 Setting2 Setting2
Setting3 Setting3 Setting3 Setting3

Is there a quick way to do this?
 
Ok, I admit... I did say quick. (And that was quick)

But is there a way to do it in Access, along the lines of a crostab query or
something?
 
I think you need to post a better example of the data - at least for me - too
many Setting1's.
 
Dear Jon:

A query to do this is possible. It can be done in MSDE as just a query
(probably a Stored Procedure). For Jet, you would need to code in VBA. I'm
not sure what needs to be "quick" about this. Are you talking about how
long it takes to run the query? Are you talking about how long it takes to
code? Are you talking about how long it will take you to learn it?

The process involves generating the code for the final query, then running
that code. As the columns names for the final query are actually in a
column in the data, you must step though the data in this column and
generate the code.

I'm going to assign names to your original data columns:
QueNum/SettingA/SettingB/SettingC

So, the first query is SELECT QueNum FROM Table ORDER BY QueNum

Stepping through this with a cursor (MSDE/SQL Server Stored Procedure) or
using a VBA recordset generates the values:

QueueNum1
QueueNum2
QueueNum3
QueueNum4

As these occur, you create the following SQL text:

SELECT
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum1") AS QueueNum1,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum2") AS QueueNum2,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum3") AS QueueNum3,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum4") AS QueueNum4
UNION ALL
SELECT
(SELECT SettingB FROM Table WHERE QueNum = "QueueNum1") AS QueueNum1,
(SELECT SettingB FROM Table WHERE QueNum = "QueueNum2") AS QueueNum2,
(SELECT SettingB FROM Table WHERE QueNum = "QueueNum3") AS QueueNum3,
(SELECT SettingB FROM Table WHERE QueNum = "QueueNum4") AS QueueNum4
UNION ALL
SELECT
(SELECT SettingC FROM Table WHERE QueNum = "QueueNum1") AS QueueNum1,
(SELECT SettingC FROM Table WHERE QueNum = "QueueNum2") AS QueueNum2,
(SELECT SettingC FROM Table WHERE QueNum = "QueueNum3") AS QueueNum3,
(SELECT SettingC FROM Table WHERE QueNum = "QueueNum4") AS QueueNum4

Hopefully you can see that the only variable in the creation of the 4 lines
is to substitute the values of the column QueNum into the above.

The number of UNIONs would not change unless the number of rows in the
results would change, that is, unless the number of data columns for
Settings changes. The number or columns, for each of which there is a
subquery, changes with the number of rows in the original table.

For Access Jet you need to change the format to include a "dummy" FROM
clause.

SELECT TOP 1
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum1") AS QueueNum1,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum2") AS QueueNum2,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum3") AS QueueNum3,
(SELECT SettingA FROM Table WHERE QueNum = "QueueNum4") AS QueueNum4
FROM Table

As shown above, add the TOP 1 and FROM Table to each of the 3 sections as
shown.

The SQL is not pretty, but the code to generate it isn't half bad. Note
that the number of columns in the finished version depends on the number of
rows in the data. This is strictly limited to 255 columns, but where are
you going to find a piece of paper that wide, anyway?

Tom Ellison
 
Try first normalizing your stored data with a union query. Once your data is
normalized, you can create a crosstab that transposes the records.
 
Back
Top