Help with a query

[theworm]

I'm new to sql so thanks for any help. We have had a small access database created for us that tracks training records. One of the reports returns all employees who are trained in a piece of equipment. However, their cert expires every 2 years and both records show up on the database.

Doesnt look a like a difficult query.
SELECT Entry.FullName, Entry.ClassTaken, Entry.Datetaken, Entry.DateExpires, Entry.NowCertified, Entry.Passyeswritten
FROM EMP INNER JOIN Entry ON EMP.FullName=Entry.FullName
WHERE entry.dateexpires > date() and emp.activeno = false;

What I would like to do is have only return the latest certification taken. IF somebody could give some suggestions of point to a location I would appreciate it.
Thanks

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[theworm]

Might be a little over my head, but I will try. Thanks for the help.

[pbaldy]

No problem, post back if you get stuck. Welcome to the site by the way!

[theworm]

Thanks Paul for your help but as I suspected, I'm a little stuck. Here is query 1 which seems to work correctly.

SELECT max(entry.dateexpires) AS maxdate, entry.fullname
FROM entry
GROUP BY entry.fullname;

Query 2 is giving me some problems at the join, but I'm not sure where to be looking.

SELECT Entry.FullName, Entry.ClassTaken, Entry.Datetaken, Entry.DateExpires, Entry.NowCertified, Entry.Passyeswritten
FROM ((entry inner join maxdateexpires on entry.dateexpires = maxdateexpires.dateexpires) INNER JOIN Entry ON EMP.FullName=Entry.FullName))
WHERE entry.dateexpires > date() and emp.activeno = false;

Thanks again.

[pbaldy]

Something is amiss in there. You have EMP in the ON but never actually listed as a table, but Entry is there twice (like it's joined to itself). The parentheses are also off (2 open, 3 close). I would take the Emp table out for now, and get it working correctly without it, then add it back. Can you post the db?

[theworm]

Ok, thanks for the pointers, I will take a closer look and repost if I still need help.
Thanks