Regarding your orignal posting:
This doesn't seem like it should be that difficult; one of the earlier
responses would be correct.
SELECT lname,fname,ssn, max(testDate)
FROM students
WHERE testDate > dateAdd(y,-2,getdate())
GROUP BY lname,fname,ssn
ORDER BY testDate
An aside: if you are going to have multiple test records, your db would be
better with a studentTests table holding ssn and testdate. Have one record
in students for ssn, lname, fname, etc., then record the individuals
multiple test instances.
-----Original Message-----
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 02, 2000 1:48 PM
To: CF-Talk
Subject: Still need Help with Query
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
