<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<title>RE: [AccessD] select most recent</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
p
{margin-right:0in;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.emailstyle18
{font-family:Arial;
color:navy;}
span.EmailStyle21
{font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body bgcolor=white lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Hi Selina,</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>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? </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>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.</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>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.</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<p class=MsoAutoSig><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Thanks,</span></font></p>
<p class=MsoAutoSig><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoAutoSig><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Eric</span></font></p>
</div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Selina Iddon
[mailto:selina@easydatabases.com.au] <br>
<b><span style='font-weight:bold'>Sent:</span></b> </span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Sunday,
February 16, 2003</span></font><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font><font
size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>4:10 PM</span></font><font
size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'><br>
<b><span style='font-weight:bold'>To:</span></b> accessd@databaseadvisors.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [AccessD] select most
recent</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Hi Again Eric</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>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?</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Cheers</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Selina</span></font></p>
</div>
<div>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width=400 style='width:300.0pt' noshade color="#df18b7"
align=center>
</span></font></div>
<div align=center>
<table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 width=400
style='width:300.0pt;margin-left:.5in'>
<tr>
<td width=10 valign=top style='width:7.5pt;padding:0in 0in 0in 0in'>
<p class=MsoNormal><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</td>
<td width=188 valign=top style='width:141.0pt;padding:0in 0in 0in 0in'>
<p class=MsoNormal><strong><b><font size=1 color="#1a51c0" face=Verdana><span
style='font-size:7.5pt;font-family:Verdana;color:#1A51C0'>Selina Iddon </span></font></b></strong><b><font
size=1 face=Verdana><span style='font-size:7.5pt;font-family:Verdana;
font-weight:bold'><br>
</span></font></b><font size=1 face=Verdana><span style='font-size:7.5pt;
font-family:Verdana'><a href="mailto:selina@easydatabases.com.au"><font
color=black><span style='color:black'>selina@easydatabases.com.au</span></font></a></span></font></p>
</td>
<td width=152 valign=top style='width:114.0pt;padding:0in 0in 0in 0in'>
<p class=MsoNormal><font size=1 face=Verdana><span style='font-size:7.5pt;
font-family:Verdana'>Ph: 0414 225 265<b><span style='font-weight:bold'><br>
</span></b>Easy Access Databases </span></font></p>
</td>
<td width=50 valign=top style='width:37.5pt;padding:0in 0in 0in 0in'>
<p class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'><img border=0 width=33
height=33 src="cid:image001.jpg@01C2D618.FC8E5260"></span></font></p>
</td>
</tr>
</table>
</div>
</div>
<blockquote style='border:none;border-left:solid black 1.5pt;padding:0in 0in 0in 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</blockquote>
</div>
</body>
</html>