[AccessD] select most recent

Eric

I'm not clear what you intend in your design. For instance, if a manager
moves from one territory to another, then the historical record must be
lost, since managerID is the PK.  So is this table meant to be a
'current state' table, or a history from which the current state may be
deduced (which I think is what you are asking)?

John


-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Eric Goetz
Sent: 16 February 2003 22:10
To: AccessD
Subject: [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