[AccessD] select most recent

RE: [AccessD] select most recentHi Again Eric
Just saw your update, if you do this you may want to make one query with 'finish_date = null' then use that query in your record source with a right join from Territories ->ManagerAsignments where nz(Manager_Name,"Open") returns what you want rather than create records with Open in them.  If the manager is an ID, and it is the primary key, this seems to mean you would need heaps of managers called "Open" for it to work?
Cheers
Selina

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

  ----- Original Message ----- 
  From: Eric Goetz 
  To: accessd at databaseadvisors.com 
  Sent: Monday, February 17, 2003 10:02 AM
  Subject: RE: [AccessD] select most recent


  Hi,



  I am thinking of the table as a history. I probably should have called the table tblManagerAssignments. If a manager moves from one territory to another, I would make a new record with the new start date. I could have normalized the StartDate and TerritoryID out from the FirstName and LastName.



  If a manager leaves a territory, and there is a time when no manager is assigned to the territory, the report should show "Open". I was intending to handle this by creating a new record with the name of "Open", and give the start date to be the date that the manager left the territory. There would be many records with the name of "Open".



  Thanks,



  Eric



  -----Original Message-----
  From: DJK(John) Robinson [mailto:djkr at msn.com] 
  Sent: Sunday, February 16, 2003 3:12 PM
  To: accessd at databaseadvisors.com
  Subject: RE: [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

   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030216/40aacaee/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/40aacaee/attachment-0002.obj>