In an Access 2003 application, how to resolve the error "You tried to execute a query that does not include the specified expression ... as part of an aggregate function?

In an Access 2003 application, how to fix the error
"You tried to execute a query that does not include the specified expression
'IIF(tbl_SMR_OpenItems.T="E","D",tbl_SMR_OpenItems.T)' as part of an
aggregate function.

SELECT tbl_SMR_OpenItems.Bank,
          IIf(tbl_SMR_OpenItems.T="E","D",tbl_SMR_OpenItems.T) AS T,
          IIf([AgeDays]<6,"1-5",
          IIf([AgeDays]<11,"6-10",
          IIf([AgeDays]<16,"11-15",
          IIf([AgeDays]<21,"16-20",
          IIf([AgeDays]<26,"21-25",
          IIf([AgeDays]<31,"26-30",
          "31+")))))) AS AgeRange,
          Sum(tbl_SMR_OpenItems.amount) AS [$],
          Count(tbl_SMR_OpenItems.Type) AS Items
          INTO tbl_SMR_OpenItemsAgeRange
          FROM tbl_SMR_OpenItems
          GROUP BY tbl_SMR_OpenItems.Bank,
          tbl_SMR_OpenItems.T,
          IIf([AgeDays]<6,"1-5",
          IIf([AgeDays]<11,"6-10",
          IIf([AgeDays]<16,"11-15",
          IIf([AgeDays]<21,"16-20",
          IIf([AgeDays]<26,"21-25",
          IIf([AgeDays]<31,"26-30",
          "31+"))))));
zimmer9Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You might also want to consider creating a table for those age ranges, something like: tbl_lst_AgeRanges (RangeGE, RangeLT, RangeText)  with values like:

RangeGE   RangeLT    RangeText
0                     6           "1-5"
6                    11          "6-10"
11                  16          "11-15"
16                  21          "16-20"
21                  26          "21-25"
26                 31           "26-30"
31                 999           "31+"

Then, in your query, you would use a non-equi join (can only be completed in the SQL view) like:

SELECT tbl_SMR_OpenItems.Bank, RangeText as AgeRange
FROM tbl_SMR_OpenItems
INNER JOIN tbl_lst_AgeRanges
ON tbl_SMR_OpenItems.[AgeDays] >= tbl_lst_AgeRanges.RangeGE
AND tbl_SMR_OpenItems.[AgeDays] < tbl_lst_AgeRanges.RangeLT

This way, you could easily change you age ranges in a table, and not have to go looking for where you did it in code or queries.
0
 
Dale FyeCommented:
You will not be able to refer to the computed field, [T], you will need to use the expression:

    IIf(tbl_SMR_OpenItems.T="E","D",tbl_SMR_OpenItems.T)

Just like you did with the expession for AgeRange

 GROUP BY tbl_SMR_OpenItems.Bank,
 IIf(tbl_SMR_OpenItems.T="E","D",tbl_SMR_OpenItems.T),
 IIf([AgeDays]<6,"1-5",
 IIf([AgeDays]<11,"6-10",
 IIf([AgeDays]<16,"11-15",
 IIf([AgeDays]<21,"16-20",
 IIf([AgeDays]<26,"21-25",
 IIf([AgeDays]<31,"26-30",
 "31+"))))));
0
All Courses

From novice to tech pro — start learning today.