SQL PROB

[mn12]

Hi,
I use access DB in my vb program,and I have SQL problem.
I have a table with 4 records:date,number and 2 other records.
each number can appear on some dates,e.x.:
number date
10 10/2003
10 1/2003
10 5/2003
20 3/2003
20 5/2003
20 6/2003

I want all the records that appear till 6/2003,
but-I want the one latest record only for each number.
e.x:
for number 20- I want the record of 6/2003.
for number 10- I want the record of 5/2003.
I want to get all the fields of each record.(not only number and date).

I have tried this query:
&quot;SELECT MAX(DATE) AS MAX_DATE, number FROM table WHERE DATE<#06/01/2003# GROUP BY number&quot;

but--it doesn't work because I want to select more than one field,and I can use &quot;GROUP BY&quot; on one field only.

can someone help me please???

thank you

mn12

 

[swampBoogie]

select * from t as tq
where dateColumn = (
  select max(dateColumn)
    from t
   where dateColumn <#06/01/2003# 
     and number = tq.number)