well it still doesnt pull just the ONE distinct record my 2 records still
come up...???
i only want the most recent of all the folks records to appear...
Stay current with what's happening on AirportNet. Subscribe to our
Announcement list today: http://www.airportnet.org/email.htm
---------------------------------------
Kelly Matthews
Internet Development Coordinator
AAAE
703.578.2509
[EMAIL PROTECTED]
http://www.airportnet.org
---------------------------------------
> -----Original Message-----
> From: Jason Powers [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, November 02, 2000 12:16 PM
> To: CF-Talk
> Subject: RE: Help with Query
>
> Can't you say something like
> SELECT name, max(date)
> FROM table
> WHERE date<@mydate-2years
> GROUP BY name
>
> Jason Powers
> Fig Leaf Software
>
> -----Original Message-----
> From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, November 02, 2000 11:51 AM
> 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]
------------------------------------------------------------------------------------------------
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]
