Display all latest Entries

  • Thread starter Thread starter DontKnow
  • Start date Start date
D

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
 
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
 
DontKnow said:
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
Click to expand...

Try this kind of thing:

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