<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1141" name=GENERATOR>
<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;}
span.EmailStyle20
{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 vLink=blue link=blue>
<DIV><SPAN class=937365601-19022003><FONT face="Comic Sans MS" color=#0000ff
size=2>T1 is an alias you use in the subquery to avoid confusing the query
engine (and yourself). If you want to dig into this stuff, get yourself a
good book on the SQL language itself. Joe Celko has written a number of
them that cover the ground very thoroughly. I have a huge library of books
on Access, SQL, Visual Basic, VBA, SQL Server and various programming
basics like systems analysis and design. I've learned a lot from trial and
error too, but I find it easier to find things in a book and study them than
grabbing stuff off a website or list and trying to fit it into what I'm doing
... unless it's Shamil's stuff, of course!
<VBG></FONT></SPAN></DIV>
<DIV><SPAN class=937365601-19022003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=937365601-19022003><FONT face="Comic Sans MS" color=#0000ff
size=2>Charlotte Foust </FONT></SPAN></DIV>
<DIV><SPAN class=937365601-19022003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Eric
Goetz [mailto:EricGoetz@egisystems.com] <BR><B>Sent:</B> Tuesday, February 18,
2003 10:55 AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] select most recent<BR><BR></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Hi
Drew,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Wow! It’s like magic.
I would never have come up with this, especially the part
“TerritoryID=T1.TerritoryID”. I still don’t really understand how it works.
I’ve looked through all my books, but it seems my library needs another book
(to the astonishment of my wife!) Will you please suggest a book that explains
this type of query?</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Thanks,</SPAN></FONT></P>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoAutoSig><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Eric</SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Drew Wutka
[mailto:DWUTKA@marlow.com] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Monday, February 17,
2003</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">9:33
PM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Okay, I
built tblTest. It has ManagerID, TerritoryID and DateAssigned as you
have below. Then I used this SQL statement to produce the results you
want:</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Select
ManagerID, TerritoryID, DateAssigned<BR>From tblTest As T1<BR>Where
DateAssigned In<BR>(Select Top 1 DateAssigned<BR>From tblTest<BR>Where
TerritoryID=T1.TerritoryID And DateAssigned<=[What Report Date would you
like to use?]<BR>Order By DateAssigned DESC);</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Good
luck,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Drew</SPAN></FONT></P></DIV>
<BLOCKQUOTE style="MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in">
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></BLOCKQUOTE></DIV></BLOCKQUOTE></BODY></HTML>