UtterAccess Discussion Forums > Syntax Error. In Query Expression ‘(select Count(*)from Master Table As B Where Master Table.12month

NewtoVBA

Dec 8 2010, 11:34 AM

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

CODE

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

Jerry Whittle

Dec 8 2010, 11:56 AM

Where and how are you trying to add that expression? I don't see it in your code example.

NewtoVBA

Dec 8 2010, 12:03 PM

I’m trying to add it in Design View in the field section

datAdrenaline

Dec 8 2010, 12:28 PM

Try This ...

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 [Yearly Total]
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");

It will give you a grid that looks something like this:

CODE

OMNi_Number  Account_Executive_Name  Yearly Total  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
       Brent                   12.00         1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 
       Brent                   12.00         1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 
       Brent                   12.00         1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00

If you want a running sum from month to month (ie: Jan shows 1.00, Feb shows 2.00, etc.), then that is a different query.

datAdrenaline

Dec 8 2010, 12:32 PM

DOH! ... I missed that you were trying to Add RANK ...

... I don't know how I did that!

datAdrenaline

Dec 8 2010, 12:44 PM

To do a Rank for a Cross Tab query ... Create a SELECT Query object, then use your Cross Tab Query Object as its source ... then your SELECT would be something like (using the field name as I showed in my sample):

SELECT CrossTabQueryName.*
, (SELECT Count(*) FROM CrossTabQueryName as CT WHERE CrossTabQueryName.[Yearly Total] > CT.[Yearly Total] GROUP BY [Yearly Total]) + 1 As Rank
FROM CrossTabQueryName
ORDER BY [Yearly Total] ASC

{Air Code!!! ... so no testing done!}

Likely to be a slow query though, just like most "Ranking" queries. I personally do Rankings in a Report, thus the hit to the database is not nearly as heavy.

NewtoVBA

Dec 8 2010, 01:38 PM

datAdrenaline,

This is the error I’m getting
Syntax error.in query expression ‘(SELECT Count(*) FROM Master Table_Total_per_Month as CT WHERE Master Table_Total_per_Month.[Yearly Total] > CT.[Yearly Total] GROUP BY [Yearly Total]) + 1

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

CODE

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

CODE

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

.
Then

CODE

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

CODE

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;

Error message also attached, if it helps

Thank you for your time and help.

NewtoVBA

Dec 8 2010, 04:15 PM

Thanks datAdrenaline. I got it figured out

datAdrenaline

Dec 8 2010, 04:53 PM

Cool!

...

Care to share the solution that worked so others may learn?

NewtoVBA

Dec 9 2010, 12:11 PM

Ranking in a query will take a long time. I assumed I was getting a error, when in face it was just taking a long time