MajP's Please Help with Syntax error or anyone that can Advise

[TJVFree]

MajP's,

I'm sorry I'm asking for you personal, but it seems your were the only one that understood what I was asking and gave me the Rank and added your advice to add a the weighted ranking.
But I’m also hoping that someone might be able to see what you did and help me with the error I’m getting now that I’m trying to incorporate the Ranking into my query

This is the error I’m getting
Syntax error. In query expression ‘(Select count(*) from Master Table as B where Master Table.12Month > B.12Month)

This is the sql I;m trying to incorporate it into

 TRANSFORM Sum([Master Table].AMOUNT_ACTUAL_LOAN) AS SumOfAMOUNT_ACTUAL_LOAN
SELECT [Master Table].orig_code AS OMNI_Number, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC AS Account_Executive_Name, Sum([Master Table].AMOUNT_ACTUAL_LOAN) AS [Total Of AMOUNT_ACTUAL_LOAN], Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0)) AS 3Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0)) AS 6Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0)) AS 9Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) AS 12Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) AS CustomerTotal
FROM [Master Table]
WHERE ((([Master Table].CORRESPONDENT_PURCHASE_DATE)>#1/1/2010#))
GROUP BY [Master Table].orig_code, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC
PIVOT Format([CORRESPONDENT_PURCHASE_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Below is what I’m trying to do. Any help will be greatly appreciated

Examples Not Real Table or Field Names, please look about real names

SELECT 
 tblOne.ID, 
 [3M]+[6M]+[9m]+[12M] AS CustomerTotal
FROM 
 tblOne
ORDER BY 
 [3M]+[6M]+[9m]+[12M];

.
Then

SELECT 
 qryCustomerTotals.ID, 
 qryCustomerTotals.CustomerTotal, 
 (Select count(*) from qryCustomerTotals as B where qryCustomerTotals.CustomerTotal > B.customerTotal) AS Rank, 
 (Select count(*) from qryCustomerTotals) AS TotalRecords,  [Rank]/([TotalRecords]-1)*5 AS 0to5Rank
FROM qryCustomerTotals;

Then to get the linear ranking from 1 to 5

SELECT 
 qryCustomerTotals.ID, 
 qryCustomerTotals.CustomerTotal, 
 (Select sum(customerTotal)from qryCustomerTotals) AS GrandTotal, 
 (select min(customerTotal) from qryCustomerTotals) AS MinTotal, 
 (select max(customerTotal) from qryCustomerTotals) AS MaxTotal, 
 5*(([CustomerTotal]-[minTotal])/([MaxTotal]-[MinTotal])) AS WeightedRank
FROM qryCustomerTotals;

Thank you for your time and help.

 

[dhookom]

CoreyVI,
It seems I recently answered a question here with what might be the same issue

  ... from Master Table ...

You do the crime (spaces and other junk in object names), you do the time (type []s around your object names).


Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Assume!! Thanks again Duane