[AccessD] select most recent

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>
 
Charlotte Foust  
 
-----Original Message-----
From: Eric Goetz [mailto:EricGoetz at egisystems.com] 
Sent: Tuesday, February 18, 2003 10:55 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] select most recent



	Hi Drew,

	 

	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?

	 

	Thanks,

	 

	Eric

	 

	-----Original Message-----
	From: Drew Wutka [mailto:DWUTKA at marlow.com] 
	Sent: Monday, February 17, 2003 9:33 PM
	To: 'accessd at databaseadvisors.com'
	Subject: RE: [AccessD] select most recent

	 

	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:

	 

	Select ManagerID, TerritoryID, DateAssigned
	From tblTest As T1
	Where DateAssigned In
	(Select Top 1 DateAssigned
	From tblTest
	Where TerritoryID=T1.TerritoryID And DateAssigned<=[What Report
Date would you like to use?]
	Order By DateAssigned DESC);

	 

	Good luck,

	 

	Drew

		 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030218/9f6973dd/attachment-0002.html>