Ranking query: What is my error?

[Dwise]

I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Roger Carlson]

I think you have the alias in the wrong spot. Try this:

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
AS Turn1
Where [2005Turnout].[Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tom Ellison]

Dear D:

The subquery you used in the SELECT clause is almost certainly intended to
reference a separate "instance" of the table Turnout. In order to reference
two instances of this table, you must do two things:

- Make each reference to a column in one of the two tables "explicit" as to
which table it references.

- Alias at least one of the two instances of the table, so there can BE a
distinct way of explicitly referencing each.

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where 2005Turnout.[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

In the above, I removed the ; from the subquery.

Now, outside the subqeury, it is unnecessary to make the table reference.
The additional copy of the table introduced in the subquery is not available
outside the subquery. I might have written the whole thing like this:

SELECT sPrecinctID, Turnout,
(SELECT Count(*) + 1
FROM 2005Turnout T1
Where T1.[Turnout] > T.[Turnout])
AS Ranking
FROM 2005Turnout AS T
ORDER BY Turnout DESC;

Above, I've used a minimal amount of aliasing, only where necessary inside
the subquery. I added 1 to the Ranking, as you may wish to have it begin
counting from 1 instead of 0. That is easily removed.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Dwise]

Thanks, but I'm still unsuccessful. Your suggested SQL produces a syntax
error (missing operator). The example from Microsoft, which is in the sample
query download is:

SELECT Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where
[HireDate] < [Emp1].[HireDate])+1 AS Seniority
FROM Employees AS Emp1
ORDER BY Emp1.HireDate;

Could I have a "jet engine" problem?

I think you have the alias in the wrong spot. Try this:

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
AS Turn1
Where [2005Turnout].[Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Dwise]

Thanks. I get the same error at "T.[Turnout]". The Jet Engine says it
doesn't recognize it as a valid field name or expression. I have had "jet
engine" trouble before. Could this be another case?

Dear D:

The subquery you used in the SELECT clause is almost certainly intended to
reference a separate "instance" of the table Turnout. In order to reference
two instances of this table, you must do two things:

- Make each reference to a column in one of the two tables "explicit" as to
which table it references.

- Alias at least one of the two instances of the table, so there can BE a
distinct way of explicitly referencing each.

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where 2005Turnout.[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

In the above, I removed the ; from the subquery.

Now, outside the subqeury, it is unnecessary to make the table reference.
The additional copy of the table introduced in the subquery is not available
outside the subquery. I might have written the whole thing like this:

SELECT sPrecinctID, Turnout,
(SELECT Count(*) + 1
FROM 2005Turnout T1
Where T1.[Turnout] > T.[Turnout])
AS Ranking
FROM 2005Turnout AS T
ORDER BY Turnout DESC;

Above, I've used a minimal amount of aliasing, only where necessary inside
the subquery. I added 1 to the Ranking, as you may wish to have it begin
counting from 1 instead of 0. That is easily removed.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from
2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Roger Carlson]

OK, try this:

SELECT 2005Turnout.sPrecinctID, 2005Turnout.Turnout, (Select Count (1) from
2005Turnout
Turn1 Where [2005Turnout].[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, but I'm still unsuccessful. Your suggested SQL produces a syntax
error (missing operator). The example from Microsoft, which is in the sample
query download is:

SELECT Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where
[HireDate] < [Emp1].[HireDate])+1 AS Seniority
FROM Employees AS Emp1
ORDER BY Emp1.HireDate;

Could I have a "jet engine" problem?

I think you have the alias in the wrong spot. Try this:

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
AS Turn1
Where [2005Turnout].[Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Dwise]

Thanks... The exact SQL you propose doesn't run, but the following does:

SELECT sPrecinctID, Turnout, (Select Count (*) from 2005Turnout AS Turn1
Where [Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout ORDER BY Turnout DESC;

It returns a zero in the Ranking column, but no error message!

OK, try this:

SELECT 2005Turnout.sPrecinctID, 2005Turnout.Turnout, (Select Count (1) from
2005Turnout
Turn1 Where [2005Turnout].[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, but I'm still unsuccessful. Your suggested SQL produces a syntax
error (missing operator). The example from Microsoft, which is in the sample
query download is:

SELECT Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where
[HireDate] < [Emp1].[HireDate])+1 AS Seniority
FROM Employees AS Emp1
ORDER BY Emp1.HireDate;

Could I have a "jet engine" problem?

I think you have the alias in the wrong spot. Try this:

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
AS Turn1
Where [2005Turnout].[Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from
2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Dwise]

It turns out that the problem was apparently that my top-level query was
built on a crosstab query. When I convert the top-level query to a table
("TNum" using a make-table query), the following delivers the correct result:

SELECT T1.sPrecinctID, T1.TurnoutPCT, (Select Count(*) From Tnum Where
[TurnoutPCT] > [T1].[TurnoutPCT])+1 AS Ranking
FROM TNum AS T1
ORDER BY T1.TurnoutPCT DESC;

Is there no other solution?

Dear D:

The subquery you used in the SELECT clause is almost certainly intended to
reference a separate "instance" of the table Turnout. In order to reference
two instances of this table, you must do two things:

- Make each reference to a column in one of the two tables "explicit" as to
which table it references.

- Alias at least one of the two instances of the table, so there can BE a
distinct way of explicitly referencing each.

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
Where 2005Turnout.[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

In the above, I removed the ; from the subquery.

Now, outside the subqeury, it is unnecessary to make the table reference.
The additional copy of the table introduced in the subquery is not available
outside the subquery. I might have written the whole thing like this:

SELECT sPrecinctID, Turnout,
(SELECT Count(*) + 1
FROM 2005Turnout T1
Where T1.[Turnout] > T.[Turnout])
AS Ranking
FROM 2005Turnout AS T
ORDER BY Turnout DESC;

Above, I've used a minimal amount of aliasing, only where necessary inside
the subquery. I added 1 to the Ranking, as you may wish to have it begin
counting from 1 instead of 0. That is easily removed.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from
2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;

 

[Dwise]

It turns out that the problem was apparently that my top-level query was
built on a crosstab query. When I convert the top-level query to a table
("TNum" using a make-table query from the crosstab), the following delivers
the correct result:

SELECT T1.sPrecinctID, T1.TurnoutPCT, (Select Count(*) From Tnum Where
[TurnoutPCT] > [T1].[TurnoutPCT])+1 AS Ranking
FROM TNum AS T1
ORDER BY T1.TurnoutPCT DESC;

Is there no other solution?

OK, try this:

SELECT 2005Turnout.sPrecinctID, 2005Turnout.Turnout, (Select Count (1) from
2005Turnout
Turn1 Where [2005Turnout].[Turnout] > [Turn1].[Turnout]) AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, but I'm still unsuccessful. Your suggested SQL produces a syntax
error (missing operator). The example from Microsoft, which is in the sample
query download is:

SELECT Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where
[HireDate] < [Emp1].[HireDate])+1 AS Seniority
FROM Employees AS Emp1
ORDER BY Emp1.HireDate;

Could I have a "jet engine" problem?

I think you have the alias in the wrong spot. Try this:

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from 2005Turnout
AS Turn1
Where [2005Turnout].[Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout ORDER BY 2005Turnout.Turnout DESC;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I followed Microsoft's instructions for creating a ranking query at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

but when I run the following, Access says: "the jet engine does not
recognize [Turn1].[Turnout] as a valid field name or expression."
[Turn1].[Turnout] by itself does not produce this error. Any suggestions?

SELECT Turn1.sPrecinctID, Turn1.Turnout, (Select Count (*) from
2005Turnout
Where [Turnout] > [Turn1].[Turnout] AS Ranking
FROM 2005Turnout AS Turn1
ORDER BY Turn1.Turnout DESC;