How to use query with aliases as source in a crosstab

G

Guest

When I use a query such as below for a crosstab query source it gives an
error message that it does not reconogize T.Column1 as valid. In the past I
have used the query to make a temporary table then the table as source for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
 
D

Duane Hookom

Crosstabs don't play well with subqueries. You may need to try something
like the following which assumes the fields are numeric. I expect you know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
 
G

Guest

My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

Duane Hookom said:
Crosstabs don't play well with subqueries. You may need to try something
like the following which assumes the fields are numeric. I expect you know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

KARL DEWEY said:
When I use a query such as below for a crosstab query source it gives an
error message that it does not reconogize T.Column1 as valid. In the past
I
have used the query to make a temporary table then the table as source for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
Click to expand...
Click to expand...
 
D

Duane Hookom

You have duplicate value of 1,2 in your data. Is this correct?

--
Duane Hookom
MS Access MVP

KARL DEWEY said:
My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

Duane Hookom said:
Crosstabs don't play well with subqueries. You may need to try something
like the following which assumes the fields are numeric. I expect you
know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

KARL DEWEY said:
When I use a query such as below for a crosstab query source it gives
an
error message that it does not reconogize T.Column1 as valid. In the
past
I
have used the query to make a temporary table then the table as source
for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

In addition, the expression in the DCount() was missing "<". Try:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=" & [Column1] & " AND [Column2]<=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP


KARL DEWEY said:
My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

Duane Hookom said:
Crosstabs don't play well with subqueries. You may need to try something
like the following which assumes the fields are numeric. I expect you
know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

KARL DEWEY said:
When I use a query such as below for a crosstab query source it gives
an
error message that it does not reconogize T.Column1 as valid. In the
past
I
have used the query to make a temporary table then the table as source
for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Works correctly.

No I do not know how to modify the expression if either column is text.

Duane Hookom said:
In addition, the expression in the DCount() was missing "<". Try:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=" & [Column1] & " AND [Column2]<=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP


KARL DEWEY said:
My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

Duane Hookom said:
Crosstabs don't play well with subqueries. You may need to try something
like the following which assumes the fields are numeric. I expect you
know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]=" &
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

When I use a query such as below for a crosstab query source it gives
an
error message that it does not reconogize T.Column1 as valid. In the
past
I
have used the query to make a temporary table then the table as source
for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If for instance Column1 was text, you would need to add some quotes:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=""" & [Column1] & """ AND [Column2]<=
" & [Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP

KARL DEWEY said:
Works correctly.

No I do not know how to modify the expression if either column is text.

Duane Hookom said:
In addition, the expression in the DCount() was missing "<". Try:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=" & [Column1] & " AND [Column2]<="
&
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP


KARL DEWEY said:
My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

:

Crosstabs don't play well with subqueries. You may need to try
something
like the following which assumes the fields are numeric. I expect you
know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]="
&
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

When I use a query such as below for a crosstab query source it
gives
an
error message that it does not reconogize T.Column1 as valid. In
the
past
I
have used the query to make a temporary table then the table as
source
for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you.

Duane Hookom said:
If for instance Column1 was text, you would need to add some quotes:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=""" & [Column1] & """ AND [Column2]<=
" & [Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP

KARL DEWEY said:
Works correctly.

No I do not know how to modify the expression if either column is text.

Duane Hookom said:
In addition, the expression in the DCount() was missing "<". Try:

SELECT T.Column1, T.Column2,
DCount("*", "[YourTable-4]", "Column1=" & [Column1] & " AND [Column2]<="
&
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY T.Column1, T.Column2;


--
Duane Hookom
MS Access MVP


My table is numeric. It did not do what I expected.
The data in table --
Column1 Column2
1 2
1 6
1 1
1 2
2 3
2 4
2 5
2 6
3 3
3 4
3 5
My query results --
Column1 Column2 Rank
1 1 1
1 2 3
1 2 3
1 6 4
2 3 1
2 4 2
2 5 3
2 6 4
3 3 1
3 4 2
3 5 3
Your query results --
Column1 Column2 Rank
1 1 1
1 2 2
1 2 2
1 6 1
2 3 1
2 4 1
2 5 1
2 6 1
3 3 1
3 4 1
3 5 1

:

Crosstabs don't play well with subqueries. You may need to try
something
like the following which assumes the fields are numeric. I expect you
know
how to modify the expression if either column is text.

SELECT Column1, Column2,
DCount("*","[YourTable-4]","Column1=" & [Column1] & " AND [Column2]="
&
[Column2]) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

--
Duane Hookom
MS Access MVP

When I use a query such as below for a crosstab query source it
gives
an
error message that it does not reconogize T.Column1 as valid. In
the
past
I
have used the query to make a temporary table then the table as
source
for
the crosstab.

SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;
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

Breaking ties in a Top n Query 9
how to use SQL case statement in acess query? 9
CrossTab Query with Min 3
crosstab query doesn't recognize expression in source query 4
help with ranking in a query 2
rank in select query not right 1
SQL to query columns that are in seconds since 1/1/1970 6
crosstab query fails when based on query with subquery? 6

Top