Get smallest date for each element in access query

So I have a table containing different elements and dates. It basically look开发者_运维技巧s 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)));

---

Try this

SELECT s1.actieElement, s1.begindatum
FROM tblActieElementLink AS s1
WHERE s1.begindatum =(SELECT MIN(s2.begindatum)
          FROM tblActieElementLink  s2
          WHERE s1.actieElement = s2.actieElement
          );

---

SELECT DISTINCT T1.actieElement, T1.beginDatum
  FROM tblActieElementLink AS T1
       INNER JOIN (
                   SELECT T2.actieElement, 
                          MIN(T2.beginDatum) AS smallest_beginDatum
                     FROM tblActieElementLink AS T2
                    GROUP
                       BY T2.actieElement
                  ) AS DT1
          ON T1.actieElement = DT1.actieElement
             AND T1.beginDatum = DT1.smallest_beginDatum;

---

Add a DISTINCT clause to your SELECT.