MS Access query

I am using MS access. I have a table named开发者_JAVA百科 CHANGES having columns

( CNO (int) , TNO (int), DATE_C).

I want to write an SQL query which displays recent date and group it only by CNO. But I also want to display TNO.

SELECT tno, cno, max(date_c)
FROM changes
WHERE [Date_c] In (SELECT [date_c] FROM changes  WHERE [date_c]<=[Enter date])
GROUP BY cno;

---

There are about seven ways to do this in SQL (because there always is :) and is an oft asked question on Stackoverflow. Here's one: (I've omitted your date_c <= [Enter date] parameter for clarity and because I can't test -- I'm not using the Access interface!):

SELECT DISTINCT C1.tno, C1.cno, 
       DT1.c_most_recent_date
  FROM changes AS C1
       INNER JOIN (
                   SELECT C2.cno, 
                          MAX(C2.c_date) AS c_most_recent_date
                     FROM changes AS C2
                    GROUP
                       BY C2.cno
                  ) AS DT1 
          ON C1.cno = DT1.cno;
         AND C1.c_date = DT1.c_most_recent_date;

And here's another:

SELECT DISTINCT C1.tno, C1.cno, 
       C1.c_date AS c_most_recent_date
  FROM changes AS C1
 WHERE NOT EXISTS (
                   SELECT *
                     FROM changes AS C2
                    WHERE C2.cno = C1.cno
                          AND C1.c_date < C2.c_date
                  );

---

That makes no sense. You cannot show TNO if it could be different for same CNO if you are grouping for CNO. If you want to show ANY TNO, you could do this:

SELECT FIRST(tno), cno, max(date_c)
FROM changes
WHERE [Date_c] In (SELECT [date_c] FROM changes  WHERE [date_c]<=[Enter date])
GROUP BY cno;

or this:

SELECT LAST(tno), cno, max(date_c)
FROM changes
WHERE [Date_c] In (SELECT [date_c] FROM changes  WHERE [date_c]<=[Enter date])
GROUP BY cno;

But usually you want to group by both, if you want to display both. (even if you said you don't want to).