Refer to aggregate function in WHERE clause (AC2007)

[AOB]

Hi there,

I have a SQL query to gather data from a number of tables (balances, accounts, currencies)

SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD

FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID])

WHERE BalanceDate = #12/10/2013#

How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)

Along the lines of :

WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000

I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead - but can't figure out how?

Any suggestions?

Thanks

AOB

 

[pr2-eugin]

That is not an Aggregate function. It is simply a Calculation, the condition you currently have will work.

 

[CazB]

As Paul said, that's not an aggregate function, it's just a calculation - so you should be able to just replace your WHERE statement with

 WHERE (((([tblBalances].[Amount]*[tblRates].[FXRate]))>1000) AND ((tblBalances.[BalanceDate])=#12/10/2013#));

 

[AOB]

Hi pr2-eugin,

When I use that condition, I get prompted for a parameter value (AmountUSD)?

Thanks

AOB

 

[CazB]

see my post above.....

you can't put criteria on a calculated field that way - you have to put the criteria on the calculation itself!

 

[AOB]

CazB,

Thanks - working now!

Just for my own benefit, what exactly is an aggregated function (as distinct from a calculation) then?

AOB

 

[CazB]

An Aggregate function is normally when you group by something, and then work out the sum, or average, or count, or max / min etc of something else based on what you've grouped on.

For example, if you wanted to work out the number of Orders a company had placed - you could group by the company and count the orders.

If you wanted to work out the total number of orders, you would just Count the orders without grouping by anything first!

Sorry if I've not explained that very well, but hopefully you get what I mean?

 

[pr2-eugin]

see my post above.....

Details.. Details.. If the OP used the Query builder, then it would not have been a problem.

Just for my own benefit, what exactly is an aggregated function (as distinct from a calculation) then?

Calculation - someField (some arithmetic/concatenation operator) someOtherField
Normally it involves two fields to get a value.

Aggregation (TOTALS) - Sum/Count/Avg/GroupBy/Min/Max(someField).
Normally involved an aggregation on one field.

 

[AOB]

Thanks guys

No, that makes sense, I get the difference now. Appreciate the tutoring!

pr2-eugin,

Point taken but my query is actually a lot more complex than what I have laid out below - I merely simplified it to make the question more understandable - and I found the query builder wasn't being very helpful in determining the SQL!

I do generally hammer the query builder first before resorting to cries for help - honest!

Thanks again

AOB

 

[MSAccessRookie]

Thanks guys

No, that makes sense, I get the difference now. Appreciate the tutoring!

pr2-eugin,

Point taken but my query is actually a lot more complex than what I have laid out below - I merely simplified it to make the question more understandable - and I found the query builder wasn't being very helpful in determining the SQL!

I do generally hammer the query builder first before resorting to cries for help - honest!

Thanks again

AOB

I am very glad that the Forum has been useful to you, but I noticed something in your response, and wanted to point out a very common misconception that many questioners have in here. Simply put, resolving a simple issue is often much different than resolving a complicated one, even if they seem to be similar in nature. The more information that is available, the better (and perhaps faster) the response can be.

Remember we are here to help when you need us.

-- Rookie

 

[AOB]

MSAccessRookie

Again, point taken - however, while in many cases I would agree with you, in others there are situations where providing a massive piece of SQL or VBA causes the question to be lost in translation. I always edit my SQL / VBA down to bare minimum (the piece I know there is an issue with)

I would consider this a fundamental step before referring to the forum. The least I can do is try to identify the problem myself first, and have a go at fixing it, instead of simply dumping everything and saying 'Please fix my horrible code'

Dropping a whole load of overlapping SELECT AS, FROM, JOIN, WHERE statements - when I know 99% of them are fine and it's only one small piece that's awry - seems, to me anyway, to be a bit lazy, and inconsiderate of the fact that it takes time to analyse all of this to identify the problem.

That's just my two cents; again, I take your point, and agree that in some cases you might think the problem is one area but is actually elsewhere. My personal opinion is that reducing the body of the thread to your core issue should be a common courtesy if you expect someone to take the time to look into it for you.

 

[AOB]

Hi guys,

Just out of curiosity...

If one can't use this :

SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD

FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID])

WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000

Is this feasible instead?

SELECT BalanceDate, AccountNumber, Ccy, Amount, AmountUSD

FROM

(SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD

FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID]))

​

WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000

And, if so, would it get around the issue of including either/both calculated & aggregated fields in the WHERE clause?

I've tried it and it seems to work for calculated fields. However, with aggregated fields (now that I am educated on the difference!), while I get the correct population of records back (i.e. those that meet the criteria), I don't seem to get values for those aggregated fields in the dataset? The fields are there, but they appear blank? (But they must have aggregated correctly as I am getting the correct population?)

Am I going down a dodgy path here or is there something I can tweak to get the result I need?

Thanks again

AOB

 

[AOB]

Here is the extended SQL with the aggregate function included...

SELECT BalanceDate, AccountNumber, Ccy, Amount, AmountUSD, AmountExplained

FROM

(SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD,

(SELECT Sum(Amount) FROM [tblIssues] AS Tmp WHERE (AccountID=[tblBalances].[AccountID] AND (RaiseDate<=[tblBalances].[BalanceDate] AND (ResolveDate>[tblBalances].[BalanceDate] OR ResolveDate Is Null)))) AS AmountExplained,
​

FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID]))

​

WHERE BalanceDate = #12/10/2013# AND AmountExplained>1000

Like I say, this returns the correct population of records but doesn't give me the values for AmountExplained for each one?