I am trying to develop query that will insert a record in a table that I use to populate a report. The record consists of a key (a number), a Description, and a count.
The key is not the problem-- it's just a number, however I can't get a description (an expression from a lookup table) and select distinct case numbers with criteria and count the results for the count field.
I've tried to do this in one query and two separate queries, but I keep getting the message, "the expression is not part of an aggregate function."
The only workaround seems to be to avoid using the lookup table by putting the description in quotes. When I do that, the Insert works, but that also means every time the description changes I will have to change the description field manually (changing the text in quotes) in multiple queries for the report instead of just changing the lookup table.
If any of you have any ideas, I'd love to hear them. Thanks!