Dear Sir or Madam,



I have a table with several fields in them for example lets call them the 

following:

         (# People) (#)

 Name   ,Customer, Table, Menus, Bill, Date

 Ford   ,   4    ,   1  ,  4   ,  1  , 09/01/01

 Robin  ,   2    ,   6  ,  1   ,  2  , 09/01/01

 Yates  ,   3    ,   3  ,  3   ,  1  , 09/15/01

 Ford   ,   2    ,   1  ,  2   ,  1  , 09/30/01



I want to group them by Name and show the listing only by the first they 

where in the restaurant.

So far I have been able to show the Dups, by name, but now I want to only 

show the Last time they where here along with the other patrons.

You can do this with two separate queries.  In the first (call it

qryLastVisit) you select Max(Date), grouped by Name, e.g.,



   SELECT Name, Max(Date) as DateLastVisit

   FROM tblMyTable

   GROUP BY NAME ;



In the second, you join qryLastVisit to your table, and select the rest of

the fields, e.g.,



   SELECT t.*

   FROM   tblMyTable as t INNER JOIN

          qryLastVisit as q

   ON     t.Name = q.Name AND

          t.Date = q.DateLastVisit ;



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...]

Sent: Thursday, November 15, 2001 8:03 AM

To: Access

Subject: [access] GroupBY





Dear Sir or Madam,



I have a table with several fields in them for example lets call them the 

following:

         (# People) (#)

 Name   ,Customer, Table, Menus, Bill, Date

 Ford   ,   4    ,   1  ,  4   ,  1  , 09/01/01

 Robin  ,   2    ,   6  ,  1   ,  2  , 09/01/01

 Yates  ,   3    ,   3  ,  3   ,  1  , 09/15/01

 Ford   ,   2    ,   1  ,  2   ,  1  , 09/30/01



I want to group them by Name and show the listing only by the first they 

where in the restaurant.

So far I have been able to show the Dups, by name, but now I want to only 

show the Last time they where here along with the other patrons.