Syntax error. In query expression ‘(Select count(*)from Master Table a

[TJVFree]

Syntax error. In query expression ‘(Select count(*)from Master Table as B where Master Table.12Month > B.12Month);

I’m receiving this syntax error when I’m trying to add:
“Rank: (Select count(*) from qryCustomerTotals as B where qryCustomerTotals.CustomerTotal > B.customerTotal)”

Is there a simple way to correct this?

Thanks in advance for any help
TCB

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");

 

[PHV]

(Select count(*) from [!][[/!]Master Table[!]][/!] as B where [!][[/!]Master Table[!]][/!].12Month > B.12Month)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You do the crime (spaces and other junk in object names), you do the time (typing all those []s).

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

I'm still getting the same error. any ideas of what I can try next. thanks

 

[TJVFree]

Duane, do you have a recommendation for what I can try next?

 

[PHV]

What is the EXACT new error message you get ACTUALLY ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TJVFree]

Syntax error. In query expression ‘(Select count(*)from [Master Table] as B where [Master Table].12Month > B.12Month);

 

[PHV]

Why a semicolon ?
Why not a space before from ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886