[AccessD] select most recent

Hi Selina,

 

Your suggestion seems to work well if I want to get the managers for the
current date. I also need to select the managers that were active on
previous dates. So, if a new manager for a territory started at the
beginning of this year, and the report was run as of the end of last
year, I would need to select the manager that was active at the end of
last year. That manager's end date is not null. How do I select that
one? 

 

I originally had end dates in the design, but decided the rule about
having only one manager active at a time made the data redundant and
created the difficulty of synchronizing one manager's end date with
another's start date. It looks like I may have to add it back in and
deal with the validation of end dates and start dates.

 

I could probably make it work by add the end date back in. I just want
to be sure I'm not missing some clever SQL feature that could solve this
problem. Maybe there is a way to calculate the end date.

 

Thanks,

 

Eric

 

-----Original Message-----
From: Selina Iddon [mailto:selina at easydatabases.com.au] 
Sent: Sunday, February 16, 2003 4:10 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] select most recent

 

Hi 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 <mailto:selina at easydatabases.com.au> 

Ph: 0414 225 265
Easy Access Databases 

 

	 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030217/a7a75993/attachment-0002.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 1219 bytes
Desc: image001.jpg
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030217/a7a75993/attachment-0002.jpg>