[AccessD] select most recent

Hi Eric
Why not put a 'finish date' in the table also so that you can view historical data and then just set your query for 'finish date' = null, thus giving only the current Territory Managers. 
Can a Manager only be in one Territory?  With using the Manager as the PK it is very restrictive?  If he moves, do you first have to delete him from one Territory to give him another?  If he has a second term, what happens to the first record/historical data?  If you have a table autonumber as the ID and then validate the Manager/Territory/dates in code on the before_update it may be easier and give you a lot more control.
Hope this helps
Selina


--------------------------------------------------------------------------------
        Selina Iddon 
      selina at easydatabases.com.au
     Ph: 0414 225 265
      Easy Access Databases   

  ----- Original Message ----- 
  From: Eric Goetz 
  To: AccessD 
  Sent: Monday, February 17, 2003 8:09 AM
  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




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030216/a7b72a04/attachment-0002.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: small_logo.jpg
Type: application/octet-stream
Size: 1219 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030216/a7b72a04/attachment-0002.obj>