Crosstab

M

Mike

I'm trying to rank the results of a crosstab query. I've tried using the
information provided from Article ID 208946 but I keep getting an error
message saying the Microsoft Jet database engine does not recognize the field
name. I have double and triple checked to ensure there are no spelling
mistakes and that the field names are exactly the same.
 
M

Mike

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last Name],
[Cadet Information].[ID Number], [Cadet Information].Squad, [Academic Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam Average])+1 AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] = [Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam Scores_Crosstab] AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;
 
D

Duane Hookom

Do you actually have a table or query named "Academic Average Query"? It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Mike said:
Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last Name],
[Cadet Information].[ID Number], [Cadet Information].Squad, [Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam Average])+1 AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] = [Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam Scores_Crosstab] AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

Duane Hookom said:
Feel free to share your SQL view.
Click to expand...
Click to expand...
 
M

Mike

I've tried it by creating a new query (Academic Average Query) based on the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase Exam
Scores_Crosstab". For some reason I can't get the rank query to recognize
the calculated field of the crosstab query.

Duane Hookom said:
Do you actually have a table or query named "Academic Average Query"? It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Mike said:
Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last Name],
[Cadet Information].[ID Number], [Cadet Information].Squad, [Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam Average])+1 AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] = [Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam Scores_Crosstab] AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

Duane Hookom said:
Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've tried using
the
information provided from Article ID 208946 but I keep getting an error
message saying the Microsoft Jet database engine does not recognize the
field
name. I have double and triple checked to ensure there are no spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Is the "calculated field of the crosstab query" a derived column? Have you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

Mike said:
I've tried it by creating a new query (Academic Average Query) based on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase Exam
Scores_Crosstab". For some reason I can't get the rank query to recognize
the calculated field of the crosstab query.

Duane Hookom said:
Do you actually have a table or query named "Academic Average Query"? It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Mike said:
Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad, [Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've tried
using
the
information provided from Article ID 208946 but I keep getting an
error
message saying the Microsoft Jet database engine does not recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
M

Mike

Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a Column
Heading for the calculated field (average of X amount of scores), the name of
the Column would not change.

Duane Hookom said:
Is the "calculated field of the crosstab query" a derived column? Have you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

Mike said:
I've tried it by creating a new query (Academic Average Query) based on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase Exam
Scores_Crosstab". For some reason I can't get the rank query to recognize
the calculated field of the crosstab query.

Duane Hookom said:
Do you actually have a table or query named "Academic Average Query"? It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad, [Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've tried
using
the
information provided from Article ID 208946 but I keep getting an
error
message saying the Microsoft Jet database engine does not recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I doubt you can reference a derived column name in another query without
specifying the column name in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Mike said:
Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a Column
Heading for the calculated field (average of X amount of scores), the name
of
the Column would not change.

Duane Hookom said:
Is the "calculated field of the crosstab query" a derived column? Have
you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

Mike said:
I've tried it by creating a new query (Academic Average Query) based on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase
Exam
Scores_Crosstab". For some reason I can't get the rank query to
recognize
the calculated field of the crosstab query.

:

Do you actually have a table or query named "Academic Average Query"?
It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad,
[Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam
Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam
Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've tried
using
the
information provided from Article ID 208946 but I keep getting an
error
message saying the Microsoft Jet database engine does not
recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
M

Mike

How do I specify the column name. It won't let me do it in the Column
Heading property.

Duane Hookom said:
I doubt you can reference a derived column name in another query without
specifying the column name in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Mike said:
Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a Column
Heading for the calculated field (average of X amount of scores), the name
of
the Column would not change.

Duane Hookom said:
Is the "calculated field of the crosstab query" a derived column? Have
you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I've tried it by creating a new query (Academic Average Query) based on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase
Exam
Scores_Crosstab". For some reason I can't get the rank query to
recognize
the calculated field of the crosstab query.

:

Do you actually have a table or query named "Academic Average Query"?
It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad,
[Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam
Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab] AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam
Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've tried
using
the
information provided from Article ID 208946 but I keep getting an
error
message saying the Microsoft Jet database engine does not
recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

What do you mean by "It won't let me do it"

--
Duane Hookom
MS Access MVP


Mike said:
How do I specify the column name. It won't let me do it in the Column
Heading property.

Duane Hookom said:
I doubt you can reference a derived column name in another query without
specifying the column name in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Mike said:
Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a Column
Heading for the calculated field (average of X amount of scores), the
name
of
the Column would not change.

:

Is the "calculated field of the crosstab query" a derived column? Have
you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I've tried it by creating a new query (Academic Average Query) based
on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase
Exam
Scores_Crosstab". For some reason I can't get the rank query to
recognize
the calculated field of the crosstab query.

:

Do you actually have a table or query named "Academic Average
Query"?
It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet
Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad,
[Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic
Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam
Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab]
AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam
Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've
tried
using
the
information provided from Article ID 208946 but I keep getting
an
error
message saying the Microsoft Jet database engine does not
recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
M

Mike

The fields I want to rank are "Row Headings" in the Crosstab query.

Duane Hookom said:
What do you mean by "It won't let me do it"

--
Duane Hookom
MS Access MVP


Mike said:
How do I specify the column name. It won't let me do it in the Column
Heading property.

Duane Hookom said:
I doubt you can reference a derived column name in another query without
specifying the column name in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a Column
Heading for the calculated field (average of X amount of scores), the
name
of
the Column would not change.

:

Is the "calculated field of the crosstab query" a derived column? Have
you
entered the column heading values into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I've tried it by creating a new query (Academic Average Query) based
on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the "Phase
Exam
Scores_Crosstab". For some reason I can't get the rank query to
recognize
the calculated field of the crosstab query.

:

Do you actually have a table or query named "Academic Average
Query"?
It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet
Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad,
[Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic
Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam
Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam Scores_Crosstab]
AS
[Academic Average Query] ON [Cadet Information].[Auto Number] =
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam
Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've
tried
using
the
information provided from Article ID 208946 but I keep getting
an
error
message saying the Microsoft Jet database engine does not
recognize
the
field
name. I have double and triple checked to ensure there are no
spelling
mistakes and that the field names are exactly the same.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I'm too old to work back through your various responses. Can you provide the
query names, SQL views, and error message? Does the error appear in the
query or when you use it in a report?

--
Duane Hookom
MS Access MVP


Mike said:
The fields I want to rank are "Row Headings" in the Crosstab query.

Duane Hookom said:
What do you mean by "It won't let me do it"

--
Duane Hookom
MS Access MVP


Mike said:
How do I specify the column name. It won't let me do it in the Column
Heading property.

:

I doubt you can reference a derived column name in another query
without
specifying the column name in the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Yes. It is the average of X amout of scores.
Currently, I have no Column Headings. When I tried to enter a
Column
Heading for the calculated field (average of X amount of scores),
the
name
of
the Column would not change.

:

Is the "calculated field of the crosstab query" a derived column?
Have
you
entered the column heading values into the Column Headings
property?

--
Duane Hookom
MS Access MVP
--

I've tried it by creating a new query (Academic Average Query)
based
on
the
"Phase Exam Scores_Crosstab" and by creating an alias for the
"Phase
Exam
Scores_Crosstab". For some reason I can't get the rank query to
recognize
the calculated field of the crosstab query.

:

Do you actually have a table or query named "Academic Average
Query"?
It
looks like you are creating this name as an alias for Phase Exam
Scores_Crosstab.

--
Duane Hookom
MS Access MVP
--

Here is the SQL view

SELECT [Cadet Information].[Auto Number], [Cadet
Information].[Last
Name],
[Cadet Information].[ID Number], [Cadet Information].Squad,
[Academic
Average
Query].[Phase Exam Average], (Select Count(*) from [Academic
Average
Query]
Where [Phase Exam Average] > [AcademicAverage1].[Phase Exam
Average])+1
AS
Rank
FROM ([Cadet Information] LEFT JOIN [Phase Exam
Scores_Crosstab]
AS
[Academic Average Query] ON [Cadet Information].[Auto Number]
=
[Academic
Average Query].[Auto Number]) INNER JOIN [Phase Exam
Scores_Crosstab]
AS
AcademicAverage1 ON [Cadet Information].[Auto Number] =
AcademicAverage1.[Auto Number]
ORDER BY [Academic Average Query].[Phase Exam Average] DESC;

:

Feel free to share your SQL view.

--
Duane Hookom
MS Access MVP
--

I'm trying to rank the results of a crosstab query. I've
tried
using
the
information provided from Article ID 208946 but I keep
getting
an
error
message saying the Microsoft Jet database engine does not
recognize
the
field
name. I have double and triple checked to ensure there are
no
spelling
mistakes and that the field names are exactly the same.
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

Crosstab query error 1
crosstab query with parameter query 5
Crosstab query... 2
USING PARAMETERS IN A CROSSTAB QUERY 4
crosstab query parameters for dates 1
Crosstab 3
Crosstab query using union query has subquery 0
Parameter Queries in Crosstab Query 2

Top