SELECT tbl_TRAINREG.EMPKEY, tbl_TRAININGS.TRAINCATKEY, tbl_TRAININGS.TRAINDATE FROM tbl_TRAININGS INNER JOIN tbl_TRAINREG ON tbl_TRAININGS.TRAINKEY = tbl_TRAINREG.TRAINKEY WHERE (tbl_TRAINREG.TRAINSTATUS = "COMPLETED") And (tbl_TRAININGS.TRAINDATE IN (SELECT Top 2 TRAINDATE FROM tbl_TRAININGS As T2 WHERE T2.TRAINKEY = tbl_TRAINREG.TRAINKEY ORDER BY TRAINDATE Desc))
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Melissa Sent: Tuesday, November 22, 2005 6:10 PM To: [email protected] Subject: [ms_access] Top 2 dates by groups I have a database of employees and trainings and a join table which shows who attended what trainings and what was the result. I need a query that shows me the last 2 times someone attended a certain type of training. I can use max to get the most recent time but I need the top 2 times each person came to each training type. to get me all trainings a person completed I have SELECT tbl_TRAINREG.EMPKEY, tbl_TRAININGS.TRAINCATKEY, tbl_TRAININGS.TRAINDATE FROM tbl_TRAININGS INNER JOIN tbl_TRAINREG ON tbl_TRAININGS.TRAINKEY = tbl_TRAINREG.TRAINKEY WHERE (((tbl_TRAINREG.TRAINSTATUS)="COMPLETED")) GROUP BY tbl_TRAINREG.EMPKEY, tbl_TRAININGS.TRAINCATKEY, tbl_TRAININGS.TRAINDATE; How can I narrow this to get only the top 2 traindates? Or is there a way to eliminate the max and then max on that query to get the second to last date? ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
