[AccessD] select most recent

Hi,

 

I'm using AXP to work with sales data. I have a table of sales
territories and a table of sales managers. Only one manager can be
assigned to a territory at a time. The managers table has the following
fields:

 

ManagerID

TerritoryID

LastName

FirstName

StartDate

 

ManagerID is the primary key, and TerritoryID is the foreign key. Given
a date, I need to generate a list of territories and the most recently
assigned manager. I can select the manager that started before the date.

 

SELECT tblTerritories.Name, tblManagers.LastName, tblManagers.StartDate

FROM tblManagers RIGHT JOIN tblTerritories ON tblManagers.TerritoryID =
tblTerritories.TerritoryID

WHERE (((tblManagers.StartDate)<=[Enter Report Date]));

 

I need help limiting the result to the single most recent manager for
each territory. I would greatly appreciate any suggestions.

 

Thanks,

 

Eric

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030216/98dd2e23/attachment-0001.html>