Get smallest date for each element in access query
- by skerit
So I have a table containing different elements and dates.
It basically looks like this:
actieElement beginDatum
1 1/01/2010
1 1/01/2010
1 10/01/2010
2 1/02/2010
2 3/02/2010
What I now need is the smallest date for every actieElement.
I've found a solution using a simple GROUP BY statement, but that way the query loses its scope and you can't change anything anymore.
Without the GROUP BY statement I get multiple dates for every actieElement because certain dates are the same.
I thought of something like this, but it also does not work as it would give the subquery more then 1 record:
SELECT s1.actieElement, s1.begindatum
FROM tblActieElementLink AS s1
WHERE (((s1.actieElement)=(SELECT TOP 1 (s2.actieElement)
FROM tblActieElementLink s2
WHERE s1.actieElement = s2.actieElement
ORDER BY s2.begindatum ASC)));
