Solved

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?

Posted on 2014-02-10
2
1,827 Views
Last Modified: 2014-02-10
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+"))))));
0
Comment
Question by:zimmer9
  • 2
2 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39847518
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39847546
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question