Display all latest Entries

[DontKnow]

Hi Guys,

I have a database that contains a table called Tenant that has a field
called Address and also a field called DateVacated. What i want to be able
to do is show for each unique address the latest date for that address. The
table contains Many Addresses some of which are the same but have different
dates. For Example I have in a Table called Tenant, (The Address and
DateVacated Fields shown):

100 Morgan street 27 Feb 09
100 Morgan street 2 Jun 09
100 Morgan street 21 Dec 09
100 Morgan street 15 Oct 09
10 Burke Street 19 Oct 09
10 Burke Street 21 Nov 09
10 Burke Street 22 Dec 09

What I want to be able to do is show the following only:
100 Morgan street 21 Dec 09
10 Burke Street 22 Dec 09

Can anyone help me via query??

I know this is difficult!!

many thnaks for your help!!

Cheers

 

[Tom van Stiphout]

On Thu, 12 Nov 2009 21:09:01 -0800, DontKnow

You need to write a Totals query, using the Sigma button on the query
design form.
Group by Address
Max DateVacated

That's it!

-Tom.
Microsoft Access MVP

 

[Marshall Barton]

I have a database that contains a table called Tenant that has a field
called Address and also a field called DateVacated. What i want to be able
to do is show for each unique address the latest date for that address. The
table contains Many Addresses some of which are the same but have different
dates. For Example I have in a Table called Tenant, (The Address and
DateVacated Fields shown):

100 Morgan street 27 Feb 09
100 Morgan street 2 Jun 09
100 Morgan street 21 Dec 09
100 Morgan street 15 Oct 09
10 Burke Street 19 Oct 09
10 Burke Street 21 Nov 09
10 Burke Street 22 Dec 09

What I want to be able to do is show the following only:
100 Morgan street 21 Dec 09
10 Burke Street 22 Dec 09

Try this kind of thing:

SELECT Address, Max(DateVacated) As VacatedSince
FROM Tenant
GROUP BY Address