try...
SELECT <filedlist> FROM <table name>
WHERE testdate in (SELECT MAX(testdate) FROM <table name> GROUP BY testdate)
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 419 4235
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: 02 November 2000 16:51
To: CF-Talk
Subject: Help with Query
Ok I will lay this out best i can... I have a database I am dealing just
with one small table.
In that table we have:
Auto_ID FirstName LastName SocialSecNum TestName TestDate
If someone takes a test, we will use Driver Training as an example. They
take
the test and pass and their info is automatically entered in the database.
In 2 years
they will have to take the test again. I want to build a report so they can
see
who is up for RENEWAL.
Now hypothetically after 5 years or so a specific test taker may show up
more than
once. For instance if I took the test 4 years ago, then 2 years ago I would
have
2 records like:
AutoID: 3
First Name: Kelly
Last Name: Matthews
SSN: 1111
Test Name: Driver Training
Test Date: 11/07/96
AutoID: 302
First Name: Kelly
Last Name: Matthews
SSN: 1111
Test Name: Driver Training
Test Date: 11/07/98
Pretty much the same record but different test dates... To create a "Who
needs to retake their test" report.
I need to determine the LAST time each person took the test (meaning the
most recent test date) and then
if its MORE than 2 years ago have them show up on the renewal page. I found
the query difficult
because obviously there will be multiple records for the same people. So if
i query the table and it finds
2 records for kelly matthews, along with multiple records for other people,
I somehow need it to
choose the most RECENT record and then do a check to see if its older than 2
years. So in essence
on the report I would want Kelly Matthews to show up ONCE like:
Kelly Matthews - Last Test date: 01/01/98 - Up for renewal in 6 days.
Any suggestions would be appreciated.
Kelly
----------------------------------------------------------------------------
--------------------
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]
