You have to use a subquery to get the first date for each value of another
field in the table. For example if you have a table Orders and you want to
find the first order for each CustomerID then the query would be like this:
SELECT *
FROM Orders AS O1
WHERE OrderDate =
(SELECT MIN(OrderDate)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID);
By giving each instance of the Orders table a separate alias, O1 and O2,
this distinguishes between the two instances and enables the subquery to be
correlated with the outer query, so only rows where the outer query's
OrderDate matches the earliest OrderDate for each customer will be returned.
You can also do it by creating a query grouped by CustomerID and returning
the earliest OrderDate for each customer:
SELECT CustomerID, MIN(OrderDate) As FirstOrderDate
FROM Orders
GROUP BY CustomerID;
Then join this query to the Orders table on the CustomerID and
OrderDate/FirstOrderDate columns:
SELECT Orders.*
FROM Orders INNER JOIN qryFirstOrders
ON Orders.CustomerID = qryFirstOrders.CustomerID
AND Orders.OrderDate = qryFirstOrders.FirstOrderDate;
A single query with a subquery is a more elegant solution, however.
Ken Sheridan
Stafford, England
SelfTaught and StillLearning said:
Thanks, but I still get the "Cannot have aggregate function in WHERE clause",
using
Min([Count of Episodes]![Date]) as critieria, either in the Date field or
the unique identifier field.
geebee said:
hi,
You would use the MIN Function...
Min([Count of Episodes]![Date])
geebee
:
I have a large table, with many instances of data. Each record has an
identifier field and a date field. I want to find the first occurence of the
record by the date. I tried using a First([Count of Episodes]![Date])
expression as criteria in the date field but get this error message: Cannot
have aggregate function in WHERE clause.
How do I search for the first occurance of each unique value in the
indentifer field?
