Return Last

[Guest]

I have an Access 2003 database front-end (running in SQL Compatible mode)
linked to SQL Server 2000 tables.

I have the following tables:

tblVolunteer
iVolunteerID
cVolunteerName

tblStudy
iStudyID
cStudyCode
dStartDate

and a Junction table

tblVolunteer_Study
iVolunteerID
iStudyID

I am trying to write a query that returns the iVolunteerID and cStudyCode
for the last study that the volunteer attended. Have looked at Michel Walsh's
excellent article on the Access Web, but have been unable to apply it
successfully to my situation - would ideally like a single query solution.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb" which discusses this problem and offers
two different solutions, one of them a single-query solution. Your
situation is complicated by the double join, but I think the same solution
applies. If you have any question about the sample, contact me directly via
the contact information on the site.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks Roger. I created a query to flatten the tables first and then used the
approach you suggested.

--
Peter Schmidt
Ross-on-Wye, UK

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb" which discusses this problem and offers
two different solutions, one of them a single-query solution. Your
situation is complicated by the double join, but I think the same solution
applies. If you have any question about the sample, contact me directly via
the contact information on the site.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L