Help FInd Syntax error in short Query?

[Phil]

SELECT

(SELECT COUNT(*)
FROM TOP100-RL-ReportStep2 Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR(Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty AND Q1.short_desc >
Q.short_desc))AS RANK,*

FROM TOP100-RL-ReportStep2 AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;

Access is pointing to the inner SELECT statement, (seperated for
readability,) as the culprit. I can't find it. Everythign was
cut&pasted, so I know speling of field names is correct.

Any Ideas?

Phil

 

[Tom Ellison]

Dear Phil:

SELECT
(SELECT COUNT(*)
FROM TOP100-RL-ReportStep2 Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR (Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty
AND Q1.short_desc > Q.short_desc))
AS RANK,
*
FROM TOP100-RL-ReportStep2 AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;

As far as I can see, you were just missing the SELECT to start with. The
SELECT within the parens is a subquery giving the value for the RANK, but
there would still be an outer SELECT.

Tom Ellison

 

[John Vinson]

SELECT

(SELECT COUNT(*)
FROM TOP100-RL-ReportStep2 Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR(Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty AND Q1.short_desc >
Q.short_desc))AS RANK,*

FROM TOP100-RL-ReportStep2 AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;

Access is pointing to the inner SELECT statement, (seperated for
readability,) as the culprit. I can't find it. Everythign was
cut&pasted, so I know speling of field names is correct.

Any Ideas?

Phil

I think Tom missed the first SELECT there - perhaps the problem is
that you have special characters (the - signs) in the name of the
query. Does it help to change this to

SELECT
(SELECT COUNT(*)
FROM [TOP100-RL-ReportStep2] AS Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR(Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty AND Q1.short_desc >
Q.short_desc))AS RANK,*
FROM [TOP100-RL-ReportStep2] AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;


John W. Vinson[MVP]

 

[Tom Ellison]

Dear John:

Yes, I did. Much better, John!

Tom Ellison

SELECT

(SELECT COUNT(*)
FROM TOP100-RL-ReportStep2 Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR(Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty AND Q1.short_desc >
Q.short_desc))AS RANK,*

FROM TOP100-RL-ReportStep2 AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;

Access is pointing to the inner SELECT statement, (seperated for
readability,) as the culprit. I can't find it. Everythign was
cut&pasted, so I know speling of field names is correct.

Any Ideas?

Phil

I think Tom missed the first SELECT there - perhaps the problem is
that you have special characters (the - signs) in the name of the
query. Does it help to change this to

SELECT
(SELECT COUNT(*)
FROM [TOP100-RL-ReportStep2] AS Q1
WHERE Q1.SumOfSumOfship_qty < Q.SumOfSumOfship_qty
OR(Q1.SumOfSumOfship_qty = Q.SumOfSumOfship_qty AND Q1.short_desc >
Q.short_desc))AS RANK,*
FROM [TOP100-RL-ReportStep2] AS Q
ORDER BY SumOfSumOfship_qty DESC, short_desc, item_id;


John W. Vinson[MVP]

 

[Phil]

Yes he did, and yes it does. Thank you very much.