<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//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;}
/* 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;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body 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'><!-- Converted from text/plain format -->Hi
Gustav,</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'>Thank you for taking up my question. That
comes tantalizingly close. The trouble is that with the [ManagerID] in the
GROUP BY, I end up with most of the managers that have been assigned to the
territory prior to [DateSelect] instead of just the most recent one. If a
manager had been in the territory more than once, only the most recent
assignment is returned. So I do get some filtering.</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'>My sample data looks like this:</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'>ManagerID</span></font><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Territory</span></font><font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> ID DateAssigned</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1
1
11/1/2002</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>2
1
12/1/2002</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1
1
1/1/2003</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>3
2
1/1/2003</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>4
2
2/1/2003</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'>For a report as of </span></font><font size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'>1/31/2003</span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>, I am trying to get:</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'>ManagerID</span></font><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Territory</span></font><font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> ID DateAssigned</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1
1
1/1/2003</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>3
2
1/1/2003</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 use this:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><SQL></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>SELECT </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> tblManagerAssignments.TerritoryID, </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> tblManagerAssignments.MangerID, </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> Max(tblManagerAssignments.DateAssigned)
AS MaxOfDateAssigned</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>FROM tblManagerAssignments</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>WHERE (((tblManagerAssignments.DateAssigned)<=#1/31/2003#))</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>GROUP BY </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> tblManagerAssignments.TerritoryID, </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> tblManagerAssignments.MangerID;</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'></SQL></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 end up with this:</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'>ManagerID</span></font><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Territory</span></font><font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> ID DateAssigned</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>2
1
12/1/2002</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1
1
1/1/2003</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>3
2
1/1/2003</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 add a [DateCancelled] field, but I
just don’t feel right about a design that incorporates NULL fields. Maybe
I could calculate the [DateCancelled] field. Got any more ideas?</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> Gustav Brock
[mailto:gustav@cactus.dk] <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'>Monday,
February 17, 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'>2:16 AM</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> Eric Goetz<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>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>Hi Eric</span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>That could be something like:</span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'><SQL></span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>PARAMETERS</span></font> <br>
<font size=2><span style='font-size:10.0pt'> DateSelect DateTime;</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>SELECT</span></font> <br>
<font size=2><span style='font-size:10.0pt'> TerritoryID,</span></font> <br>
<font size=2><span style='font-size:10.0pt'> ManagerID,</span></font> <br>
<font size=2><span style='font-size:10.0pt'> Max(DateAssigned) AS
DateAssigned</span></font> <br>
<font size=2><span style='font-size:10.0pt'>FROM</span></font> <br>
<font size=2><span style='font-size:10.0pt'> tblManagerAssignments</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>WHERE</span></font> <br>
<font size=2><span style='font-size:10.0pt'> (DateAssigned <=
[DateSelect])</span></font> <br>
<font size=2><span style='font-size:10.0pt'>GROUP BY </span></font><br>
<font size=2><span style='font-size:10.0pt'> TerritoryID,</span></font> <br>
<font size=2><span style='font-size:10.0pt'> ManagerID;</span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'></SQL></span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>This, of course, assumes that a territory is assigned
to a specific</span></font> <br>
<font size=2><span style='font-size:10.0pt'>manager until assigned to another.
If assignment can be cancelled</span></font> <br>
<font size=2><span style='font-size:10.0pt'>without reassignment, you'll need
to add a new field, DateCancelled,</span></font> <br>
<font size=2><span style='font-size:10.0pt'>and add to the Where statement:</span></font>
</p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'> AND</span></font> <br>
<font size=2><span style='font-size:10.0pt'> (DateCancelled Is Null OR
DateCancelled > [DateSelect])</span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>If you wish to list territories not assigned a
manager, create a query</span></font> <br>
<font size=2><span style='font-size:10.0pt'>with all territories and an outer
join to the query above; those not</span></font> <br>
<font size=2><span style='font-size:10.0pt'>assigned will have a Null for
ManagerID. Vice versa for managers</span></font> <br>
<font size=2><span style='font-size:10.0pt'>without a territory.</span></font> </p>
<p style='margin-left:.5in'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>Please note that ManagerID and TerritoryID will both
be foreign keys.</span></font> <br>
<font size=2><span style='font-size:10.0pt'>And, as you note later, strip the
name fields etc. from this table.</span></font> </p>
</div>
</body>
</html>