SELECT Counter, Reading1 as Reading
FROM test
Union All
SELECT Counter, Reading2
FROM test
Union All
SELECT Counter, Reading3
FROM test
Union All
SELECT Counter, Reading4
FROM test
Union All
SELECT Counter, Reading5
FROM test
Union All
SELECT Counter, Reading6
FROM test
Union All
SELECT Counter, Reading7
FROM test
Union All
SELECT Counter, Reading8
FROM test
Union All
SELECT Counter, Reading9
FROM test
UNION ALL SELECT Counter, Reading10
FROM test;
TRANSFORM Min(quniSkinner.Reading) AS MinOfReading
SELECT quniSkinner.Counter
FROM quniSkinner
GROUP BY quniSkinner.Counter
ORDER BY quniSkinner.Counter
PIVOT "Col" & DCount("*","quniSkinner","Counter=" & [Counter] & " AND
Reading <=" & [Reading]) In
("col1","col2","col3","col4","col5","col6","col7","col8","col9","col10");
:
Any way you can share your sql and data types?
--
Duane Hookom
MS Access MVP
--
Duane - the union query would not run...I think because skinnerquery
is a
pass through query. I made a table from skinnerquery called test and
then
ran
the union query on test.
That worked fine....I end up with a table called "test" with columns
"counter" and "reading".
I then attempted to run the cross tab query on the "test"
table.....and
get
a "data type mismatch in criteria expression" error.
What am I doing wrong?
Thanks
Gary
:
First create your union query (quniSkinner):
SELECT Counter, Reading1 as Reading
FROM SkinnerQuery
Union All
SELECT Counter, Reading2
FROM SkinnerQuery
Union All
SELECT Counter, Reading3
FROM SkinnerQuery
Union All
SELECT Counter, Reading4
FROM SkinnerQuery
Union All
SELECT Counter, Reading5
FROM SkinnerQuery
Union All
SELECT Counter, Reading6
FROM SkinnerQuery
Union All
SELECT Counter, Reading7
FROM SkinnerQuery
Union All
SELECT Counter, Reading8
FROM SkinnerQuery
Union All
SELECT Counter, Reading9
FROM SkinnerQuery
UNION ALL SELECT Counter, Reading10
FROM SkinnerQuery;
Finally a very slow crosstab:
TRANSFORM Min(quniSkinner.Reading) AS MinOfReading
SELECT quniSkinner.Counter
FROM quniSkinner
GROUP BY quniSkinner.Counter
ORDER BY quniSkinner.Counter
PIVOT "Col" & DCount("*","quniSkinner","Counter=" & [Counter] & "
AND
Reading <=" & [Reading]) In
("col1","col2","col3","col4","col5","col6","col7","col8","col9","col10");
--
Duane Hookom
MS Access MVP
--
Duane - here is the query, counter is the unique identifier.
SELECT SkinnerQuery.Counter, SkinnerQuery.Reading1,
SkinnerQuery.Reading2,
SkinnerQuery.Reading3, SkinnerQuery.Reading4,
SkinnerQuery.Reading5,
SkinnerQuery.Reading6, SkinnerQuery.Reading7,
SkinnerQuery.Reading8,
SkinnerQuery.Reading9, SkinnerQuery.Reading10
FROM SkinnerQuery;
:
I am not sure why you would go from one un-normalized structure
to
another
but it would be very helpfull if you could provide the name of
your
"query
that has 10 columns" as well as the field from "query that has 10
columns"
that is the unique identifier for the rows.
Generically, you could create a union to normalize, then a
ranking
query
to
assign 1-10, and then a crosstab to un-normalize.
--
Duane Hookom
MS Access MVP
--
Iam trying to create a table from a query that has 10 columns
of
data
(real
numbers)
I would like to extract the data in the 10 columns row by
row.....these
would be inserted into the new table in the order of smallest
number
to
largest number.
Any help would be appreciated.
ie query looks like this:
reading1 reading2 reading3 ...........
1.234 3.456 0.123 ...........
new table looks like this:
column1 column2 column3 .............
0.123 1.234 3.456...........