Rank with Multiple Sub Categories

[Guest]

I am trying to rank some data using 5 columns as sub categories as follows:

Calender Year (yes, I know that it is spelled wrong)
Calender Month
Platform
Product Class Description
Adjusted Gross Qty

I have the following SQL based on the postings that I have read here but my
query is asking me for the value of T.[Adjusted Gross Qty] and [Customer
Profile % Business CY].[Adjusted Gross Qty]. I can not figure out what is
wrong with this.

SELECT [Customer Profile % Business CY].[Calender Year], [Customer Profile %
Business CY].[Calender Month], [Customer Profile % Business CY].Platform,
[Customer Profile % Business CY].[Product Class Description], [Customer
Profile % Business CY].[Adjusted Gross Qty], [Customer Profile % Business
CY].[Customer Id], (SELECT Count(*) FROM [Customer Profile % Business CY] AS
T WHERE T.[Calender Year]=[Customer Profile % Business CY].[Calender Year]
AND T.[Calender Month]=[Customer Profile % Business CY].[Calender Month] AND
T.[Platform]=[Customer Profile % Business CY].[Platform] AND T.[Product Class
Description]=[Customer Profile % Business CY].[Product Class Description] AND
T.[Adjusted Gross Qty]>=[Customer Profile % Business CY].[Adjusted Gross
Qty]) AS Rank
FROM [Customer Profile % Business CY];

Any help would be greatly appreciated.

Thx in Advance.

Russell

 

[Duane Hookom]

It's a penalty for spelling calander wrong and using spaces and symbols in
object names ;-)

Actually, you need to settle on the number of spaces between "Adjusted" and
"Gross".

This problem is more visible when the sql is pasted into notepad.

 

[Guest]

Thanks Duane! I should have been suspicious of the object names when I
noticed that calendar was spelled wrong. Oh the joys of other peoples data.

It's a penalty for spelling calander wrong and using spaces and symbols in
object names ;-)

Actually, you need to settle on the number of spaces between "Adjusted" and
"Gross".

This problem is more visible when the sql is pasted into notepad.

--
Duane Hookom
MS Access MVP
--

I am trying to rank some data using 5 columns as sub categories as follows:

Calender Year (yes, I know that it is spelled wrong)
Calender Month
Platform
Product Class Description
Adjusted Gross Qty

I have the following SQL based on the postings that I have read here but
my
query is asking me for the value of T.[Adjusted Gross Qty] and [Customer
Profile % Business CY].[Adjusted Gross Qty]. I can not figure out what is
wrong with this.

SELECT [Customer Profile % Business CY].[Calender Year], [Customer Profile
%
Business CY].[Calender Month], [Customer Profile % Business CY].Platform,
[Customer Profile % Business CY].[Product Class Description], [Customer
Profile % Business CY].[Adjusted Gross Qty], [Customer Profile % Business
CY].[Customer Id], (SELECT Count(*) FROM [Customer Profile % Business CY]
AS
T WHERE T.[Calender Year]=[Customer Profile % Business CY].[Calender Year]
AND T.[Calender Month]=[Customer Profile % Business CY].[Calender Month]
AND
T.[Platform]=[Customer Profile % Business CY].[Platform] AND T.[Product
Class
Description]=[Customer Profile % Business CY].[Product Class Description]
AND
T.[Adjusted Gross Qty]>=[Customer Profile % Business CY].[Adjusted Gross
Qty]) AS Rank
FROM [Customer Profile % Business CY];

Any help would be greatly appreciated.

Thx in Advance.

Russell