CODEWITHSUNDEEP
sqlms-accessselectgroup-byms-access-2010
LEBoyd
LEBoyd

Reputation: 151

2010 Access Query with nested JOIN, WHERE and GROUP BY

I appreciate everyone's help and patience as I continue learning through converting a large Excel/vba system to Access.

I have the following query:

SELECT AccountWeeklyBalances.AccountNumber,
AccountWeeklyBalances.AccountBalance,
AccountWeeklyBalances.AccountDate,
AccountMaster.AccountName,
AccountCurrentModel.Model,
ModelDetailAllHistory.Risk
FROM ((AccountWeeklyBalances 
INNER JOIN AccountMaster 
    ON AccountMaster.[AccountNumber] = AccountWeeklyBalances.AccountNumber)

  INNER JOIN AccountCurrentModel
       ON AccountWeeklyBalances.AccountNumber=AccountCurrentModel.AccountNumber)

     INNER JOIN ModelDetailAllHistory
       ON AccountCurrentModel.Model=ModelDetailAllHistory.ModelName

             WHERE AccountWeeklyBalances.AccountDate=[MatchDate]

;

This works, except I want to GROUP BY the Model. I tried adding

GROUP BY AccountCurrentModel.Model

and

GROUP BY ModelDetailAllHistory.ModelName

after the WHERE clause, but both give me an error:

Tried to execute a query that does not include the specified expression
   'AccountNumber' as part of an aggregate function.

I've read several other posts here, but cannot figure out what I've done wrong.

Upvotes: 0

Views: 382

Answers (1)

KKloke
KKloke

Reputation: 81

It depends on what you're trying to do. If you just want to sum the AccountBalance by ModelName, then all the other columns would have to be removed from the select statement. If you want the sum of each model for each account, then you would just add the AccountNumber to the GROUP BY, probably before the ModelName.

When aggregating, you can't include anything in the select list that's not either an aggregate function (min, max, sum, etc) or something you are grouping by, because there's no way to represent that in the query results. How could you show the sum of AccountBalance by ModelName, but also include the AccountNumber? The only way to do that would be to group by both AccountNumber and ModelName.

----EDIT----

After discussing in the comments I have a clearer idea of what's going on. There is no aggregation, but there are multiple records in ModelDetailAllHistory for each Model. However, the only value we need from that table is Risk, and that will always be the same per model. So we need to eliminate the duplicate Risk values. This can be done by joining into a subquery instead of joining directly into ModelDetailAllHistory 

 INNER JOIN (SELECT DISTINCT ModelName, Risk FROM ModelDetailAllHistory) mh
   ON AccountCurrentModel.Model=mh.ModelName

or

 INNER JOIN (SELECT ModelName, max(Risk) FROM ModelDetailAllHistory GROUP BY ModelName) mh
   ON AccountCurrentModel.Model=mh.ModelName

Both methods collapse the multiple Risk values into a single value per Model, eliminating the duplicate records. I tend to prefer the first option because if for some reason there were multiple Risk values for a single Model, you'd end up with duplicate records and you'd know there was something wrong. Using max() is basically choosing an arbitrary record from ModelDetailAllHistory that matches the given Model and getting the Risk value from it, since you know all the Risk values for that model should be the same. What I don't like about this method is it will hide data inconsistencies from you (e.g. if for some reason there are some ModelDetailAllHistory records for the same Model that don't have the same Risk value), and while it's nice to know you'll never ever get duplicate records, the underlying problem could end up rearing its ugly head in other unexpected ways.

Upvotes: 1

Related Questions