maybe you can shed some light got it to work by doing this: (there could be
an easier way)
This query finds all the distinct ssn's.
<CFQUERY DATASOURCE="#DSN#" name="search">
SELECT DISTINCT ssn
FROM STUDENTS
WHERE AIRPORT_CODE = 'AAE'
AND testname = 'Driver Training'
</CFQUERY>
Then i loop through (actually 2 loops the first takes the ssn and then finds
the max record id for that ssn)
the second goes back and gets all the INFO for the record id
Now the output is correct. It is pulling ONE record per person, the MOST
recent test.
However the ORDERING of the records is all screwy. I want the list to order
by test dates
but it doesnt. And of course putting ORDER BY testdate in one of the looped
queries doesnt
work since its dealing with one query at a time. I am sure i am 1. either
doing more than i need to do
or 2 missing something simple.??
<CFLOOP query="search">
<CFQUERY DATASOURCE="#DSN#" name="search1">
SELECT MAX(airport_auto_ID) AS RECORD
FROM students
WHERE ssn = '#trim(search.ssn)#'
</CFQUERY>
<CFLOOP query="search1">
<CFQUERY DATASOURCE="#DSN#" name="search2">
SELECT lname, fname, ssn, testdate
FROM students
WHERE airport_auto_ID = #search1.record#
</CFQUERY>
<CFOUTPUT query="search2">
#trim(fname)# #trim(lname)# #trim(ssn)# #dateformat(testdate,
'mm/dd/yy')#<BR>
</CFOUTPUT>
</CFloop>
</CFLOOP>
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
