February 14th, 2013, 04:53 PM
Last record for DATE - MAX function
I am having issues pulling up the last record in table (see red).
SQL = "SELECT customerid,customername, email, max(orderdate) AS max_date "&_
"FROM (SELECT Table1.customerid,Table1.customername as customername, Table1.email AS email, Table2.orderdate AS orderdate, Table3.Credits "&_
"FROM Table1 INNER JOIN Table2 ON Table1.customerid=Table2.customerid "&_
"INNER JOIN Table3 ON Table3.customerid = Table1.customerid "&_
"WHERE Table1.remove is null and max(Table3.date) not between '2/14/2012' and '2/14/2013' "&_
"and (Table1.customerid >= '"& id1 &"' and Table1.customerid <= '"& id2 &"') "&_
"and month(Table2.orderdate) between month('" & dDateFrom & "') and month('" & dDateTo & "')) "&_
"AS a GROUP BY customerid, customername, email ORDER BY 1"
I am getting this error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
For the life of me I can's seem how to do this. Any help would be appreciated.