Another Head Banger Please Help! Syntax error Issue

[TJVFree]

I'm receiving a syntax error "See Below" when I'm trying to run a Rank query. For those of you that want to see the database and how I'm running this sql in it I have attached it. I have also attached a picture of the error message I'm receiving.

To explain the end result I'm trying to get is to:
Rank the number of Apps that are within a first three months between #01/01/10# and #03/30/10#
I will do this for the 6Month_App_Count, 9Month_App_Count, and 12Month_App_Count.

I will also be looking at getting a Monthly_App_Count_Ranking "Jan" "Feb" and so on.

If you open DB look at the qryWeightedRank query

any help would be great, I’m slowly learning and all your help and advice is helping

Syntax error. In query expression’(Select count(*)from GP1_Maaster_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.3Month_App_Count >B.3Month_App_Count)

([sql]SELECT
[GP1_Master Table_Count_Apps].OMNI_Number,
[GP1_Master Table_Count_Apps].[3Month_App_Count],
(Select count(*) from GP1_Master Table_Count_Apps as B where GP1_Master Table_Count_Apps.3Month_App_Count > B.3Month_App_Count) AS Rank,
(Select count(*) from GP1_Master Table_Count_Apps) AS TotalRecords,
[Rank]/([TotalRecords]-1)*5 AS 0to5Rank,
CInt([0to5Rank]*10)/10 AS Star_Rating,
(Select sum(3Month_App_Count)from qry3Month_App_Counts) AS GrandTotal,
(select min(3Month_App_Count) from qry3Month_App_Counts) AS MinTotal, (select max(3Month_App_Count) from qry3Month_App_Counts) AS MaxTotal,
5*(([3Month_App_Count]-[minTotal])/([MaxTotal]-[MinTotal])) AS WeightedRank
FROM [GP1_Master Table_Count_Apps];([/sql]


Thanks you so much for helping me learn

 

[djj55]

I noticed you are using values that you calculate on the fly. This works in Access but I do not believe it works in SQL. (see Rank and TotalRecords)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[TJVFree]

Thanks you djj, I really appreciate you give me your time and advice, I did what you recommended but still getting a error, is there anything else you would recommend

 

[TJVFree]

It worked, thanks for the help again. Your ROCK