Help me with the query please

I have a Problem with Function Max in Access 2010
I have a table with a few columns
Let say
Code      DatePro                         name      Street                        city
100      1/11/2012      John      7th street                         los angeles
100      8/12/2012      Scott      Corn avenue      Hills
100      5/22/2012      Steve      Apple street      Oakland

I want to run a query to get the max date for Code regarding the other fields, so it should be
100      8/12/2012      Scott      Corn avenue      Hills

SELECT
Table1.Code,
Max(Table1.DatePro) AS MaxOfDatePro,
Table1.name,
Table1.Street,
Table1.city


does not work, return all three row
rfedorovAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
I think Access may require the AS keyword -
select t1.code, t1.datepro, t1.name, t.street, t.city
from table1 AS t1,
(select code, max(datepro) datepro
 from table1
 group by code) AS t2
where t1.code = t2.code
  and t1.datepro = t2.datepro;
0
 
awking00Commented:
select t1.code, t1.datepro, t1.name, t.street, t.city
from table1 t1,
(select code, max(datepro) datepro
 from table1
 group by code) t2
where t1.code = t2.code
  and t1.datepro = t2.datepro;
0
 
rfedorovAuthor Commented:
Thank you for your respond, something is not right...
0
 
rfedorovAuthor Commented:
SELECT t1.Code, t1.DatePro, t1.name, t1.Street, t1.city FROM table1 AS t1


 INNER JOIN


(SELECT table1.Code, Max(table1.DatePro) AS DatePro
FROM table1
GROUP BY table1.Code ) AS t2 ON (t1.Code = t2.Code) AND (t1.DatePro = t2.DatePro);
0
 
rfedorovAuthor Commented:
So with your help i figured out the task, thank you very much
0
All Courses

From novice to tech pro — start learning today.