Querying a query

[Saylindara]

This database will be the death of me.
I am copying a query process which has worked in the past which involved
several steps which queried queries. The end result was an iif expression -

iif([AmendedDateDue] <Date(), "Overdue", "Not overdue")

I've used Group By and Max for dates as I only want to be left with the most
recent date that a course was attended. On my previous efforts I then queried
this query with "Overdue" and that worked perfectly fine.

Now when I do that I get the message "You tried to execute a query that does
not include the specified expressed
IIf([AmendedDateDue]<Date(),"Overdue","Not overdue") = "overdue" as part of
an aggregate function.

What am I doing wrong?

 

[John Spencer]

Difficult to say without seeing your queries.

As a guess, you need to group by that expression in your query because you
have used aggregate functions (count, sum, first, last, etc) in your query.

Or Perhaps all you need to do is to use a WHERE clause in your aggregate query.
You query structure (in SQL) might look like

SELECT <<List of Fields>>
FROM <<Tables and Joins>>
WHERE [AmendedDueDate]<Date()
GROUP BY <<List of fields not using aggregate functions>>
HAVING <<filter criteria applied against aggregate functions>>

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Saylindara]

Thanks for your help. I think that's sorted it out.

Difficult to say without seeing your queries.

As a guess, you need to group by that expression in your query because you
have used aggregate functions (count, sum, first, last, etc) in your query.

Or Perhaps all you need to do is to use a WHERE clause in your aggregate query.
You query structure (in SQL) might look like

SELECT <<List of Fields>>
FROM <<Tables and Joins>>
WHERE [AmendedDueDate]<Date()
GROUP BY <<List of fields not using aggregate functions>>
HAVING <<filter criteria applied against aggregate functions>>

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

This database will be the death of me.
I am copying a query process which has worked in the past which involved
several steps which queried queries. The end result was an iif expression -

iif([AmendedDateDue] <Date(), "Overdue", "Not overdue")

I've used Group By and Max for dates as I only want to be left with the most
recent date that a course was attended. On my previous efforts I then queried
this query with "Overdue" and that worked perfectly fine.

Now when I do that I get the message "You tried to execute a query that does
not include the specified expressed
IIf([AmendedDateDue]<Date(),"Overdue","Not overdue") = "overdue" as part of
an aggregate function.

What am I doing wrong?