Create a table from a query

G

Guest

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...........
 
D

Duane Hookom

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.
 
G

Guest

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;
 
D

Duane Hookom

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");
 
G

Guest

Thank you very much Duane

Duane Hookom said:
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
--

ktm400 said:
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;
Click to expand...
Click to expand...
 
G

Guest

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

Duane Hookom said:
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
--

ktm400 said:
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;
Click to expand...
Click to expand...
 
D

Duane Hookom

Any way you can share your sql and data types?

--
Duane Hookom
MS Access MVP
--

ktm400 said:
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

Duane Hookom said:
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
--

ktm400 said:
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...........
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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");



Duane Hookom said:
Any way you can share your sql and data types?

--
Duane Hookom
MS Access MVP
--

ktm400 said:
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

Duane Hookom said:
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...........
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oh....just saw that counter in the "test" table is type text....this would
probably do it Iam guessing..?

ktm400 said:
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");



Duane Hookom said:
Any way you can share your sql and data types?

--
Duane Hookom
MS Access MVP
--

ktm400 said:
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...........
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SkinnerSaw_Thickness]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[SkinnerSaw_Thickness]
GO

CREATE TABLE [dbo].[SkinnerSaw_Thickness] (
[Counter] [bigint] IDENTITY (1, 1) NOT NULL ,
[ReadingDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReadingTime] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BlankID] [int] NOT NULL ,
[TargetThickness] [real] NOT NULL ,
[MaxThickness] [real] NOT NULL ,
[MinThickness] [real] NOT NULL ,
[ActualPanelThickness] [real] NOT NULL ,
[Reading1] [real] NOT NULL ,
[Reading2] [real] NOT NULL ,
[Reading3] [real] NOT NULL ,
[Reading4] [real] NOT NULL ,
[Reading5] [real] NOT NULL ,
[Reading6] [real] NOT NULL ,
[Reading7] [real] NOT NULL ,
[Reading8] [real] NOT NULL ,
[Reading9] [real] NOT NULL ,
[Reading10] [real] NOT NULL ,
[DistanceBetweenLasers] [real] NULL ,
[Blow] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BlowDetectorOn] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



ktm400 said:
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");



Duane Hookom said:
Any way you can share your sql and data types?

--
Duane Hookom
MS Access MVP
--

ktm400 said:
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...........
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If Counter is text, your expression won't work. I'm not sure if you have
this resolved or still need assistance.

--
Duane Hookom
MS Access MVP
--

ktm400 said:
Oh....just saw that counter in the "test" table is type text....this would
probably do it Iam guessing..?

ktm400 said:
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");



Duane Hookom said:
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...........
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Duane - I think I can take it from here.
Thanks again

Duane Hookom said:
If Counter is text, your expression won't work. I'm not sure if you have
this resolved or still need assistance.

--
Duane Hookom
MS Access MVP
--

ktm400 said:
Oh....just saw that counter in the "test" table is type text....this would
probably do it Iam guessing..?

ktm400 said:
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...........
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transforming empty fields into "0" in a query 3
SQL to query columns that are in seconds since 1/1/1970 6
Update query shift down by 1 0
Getting column values into row values (transpose) 1
return nulls from double column join 2
Autopopulate a Field in a New Record Based on a Field in a Prev Re 1
Need Table and Subtable in RDLC 5
Query to create multipe total fields 7

Top