[thelist] COLD FUSION: Query Problems - DB w/ Multiple entries

Hello all CF type people -

I have a query that isn't quite working, and I can't see why - Been looking
at it too long, likely.

The "cpr" query is pulling from a Database that has several historical
entries.  I only want to pull out the most recent entry for with the cpr
designation.  The CF code (written by my predecessor) is below.

Basically, it's not pulling people out because there are several records
with the same employee name, but a different date.  The date is stored in
dd-mmm-yy format in the database.
The "((Year([Date]))='<cfoutput>#currentYear#</cfoutput>')" is the problem,
and I'm unsure how to correct it.  Any ideas would be appreciated.

TIA,

Nicole.

-------------------------------------

<!--- determine what year this is --->
<cfset currentyear=#dateformat("#Now()#", "yyyy")#>

<!--- perform the First Aid Query --->
<cfquery name="firstAid" datasource="training">
	SELECT EmployeeInfo.LastName, EmployeeInfo.FirstName,
EmployeeInfo.Extension, TrainingDetails.Date, TrainingDetails.Description
	FROM EmployeeInfo INNER JOIN TrainingDetails ON EmployeeInfo.EmpID =
TrainingDetails.EmpID
	WHERE NOT (EmployeeInfo.Lab = 'Terminated') AND
(TrainingDetails.Description = 'First Aid')
	ORDER BY EmployeeInfo.LastName;
</cfquery>

<!--- perform the CPR Query --->
<cfquery name="cpr" datasource="training">
	SELECT EmployeeInfo.LastName, EmployeeInfo.FirstName,
EmployeeInfo.Extension, TrainingDetails.Date, TrainingDetails.Description
	FROM EmployeeInfo INNER JOIN TrainingDetails ON EmployeeInfo.EmpID =
TrainingDetails.EmpID
	WHERE NOT (EmployeeInfo.Lab = 'Terminated') AND
(TrainingDetails.Description = 'CPR') AND
((Year([Date]))='<cfoutput>#currentYear#</cfoutput>')
	ORDER BY EmployeeInfo.LastName;
</cfquery>


------------------------------------------
Nicole Toivonen
Web Developer
Xerox Research Centre of Canada
nicole.toivonen@crt.xerox.com
Intelnet: 8*286-1305
1-905-823-7091 Ext. 305