No subject

Wed Dec 28 11:38:03 CST 2011

Where TerritoryID=T1.TerritoryID And DateAssigned<=[What Report Date would
you like to use?]
Order By DateAssigned DESC);

Good luck,

Drew

------_=_NextPart_001_01C2D79C.CFF73570
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Re: [AccessD] select most recent</TITLE>

<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE>@font-face {
	font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
	COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
	COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
	COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
	COLOR: blue; TEXT-DECORATION: underline
}
P.MsoAutoSig {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoAutoSig {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoAutoSig {
	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
P {
	FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"
}
SPAN.emailstyle18 {
	COLOR: navy; FONT-FAMILY: Arial
}
SPAN.EmailStyle20 {
	COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
	page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff size=2>Quite 
frankly I haven't touched a book about Access or even remotely computer related 
since I started using Access.  I had a 'basic' Access book, that I used to 
figure out where to start looking in the help files, and from then on....no more 
books.  (With one exception.  I did check out a book on C++ this past 
summer from the library....so that's 1 book (about computer stuff) in almost 5 
years.).</FONT></SPAN></DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff size=2>It's 
not like I learn by magic, my resources are 'Trial and Error' (probably the 
biggest chunk...hehehehe), Access 97 help files (next in line), the MSDN (The 
version I have is what came with Access 2000 Developer's edition...3 CD's, 
plenty of stuff), then a lot of online resources, like the List, The Access Web 
(<A href="http://mvps.org/Access">http://mvps.org/Access</A>), 
comp.databases.ms-access (though that has gone down hill a bit lately), and 
lately Woody's lounge.</FONT></SPAN></DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff size=2>Lately 
I have been doing more 'contributing' then asking, but I learn just as much as I 
contribute from just reading other posts.  For example, the proposed 
solution I gave you is something I have never done in any of my own 
projects.  It just so happens that I had a post on a similar issue on 
Woody's lounge, that i solved using the subquery method...which is something I 
just remember reading about somewhere (can't remember where to be truthful), so 
I looked it up in Access 97's help files, and whalla.  Right after I posted 
to the lounge, I read your last post, so I posted a modified version for 
you.</FONT></SPAN></DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff size=2>I am 
not recommending that you forsake 'book knowledge', I just can't give you any 
good titles.</FONT></SPAN></DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=184260622-18022003><FONT face=Arial color=#0000ff 
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
  <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 
  size=2>-----Original Message-----<BR><B>From:</B> Eric Goetz 
  [mailto:EricGoetz at egisystems.com]<BR><B>Sent:</B> Tuesday, February 18, 2003 
  12:55 PM<BR><B>To:</B> accessd at databaseadvisors.com<BR><B>Subject:</B> RE: 
  [AccessD] select most recent<BR><BR></FONT></DIV>
  <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 at 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 at 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>

------_=_NextPart_001_01C2D79C.CFF73570--