List records only once

[Chris]

Anyone that can help, please do. I will try to explain
myself well.

The table shows maintenance on an equipment item with the
corresponding serial#. The maintenance is input into the
table. The certifications are due every year, so I have
filtered the date by (date - 275), which was meant to show
what will be due for certification in 3 months time
(Hence, show the equipment items that had their
certification done 9 months ago). I also want to filter
out any record that has "no" in the certification column,
and I want only to show the latest certification for each
serial number (if it is due in the next three months).
The table looks like this (for example):

Current date = Nov 5, 2003

Table name: HPTE_Maintenance

Serial# Date Certification "(Yes/No) Field"
1 Sept 1, 2000 yes
1 Sept 1, 2001 yes
2 Oct 1, 2002 yes
3 Aug 1, 1999 no
3 Aug 1, 2000 yes
3 Aug 1, 2001 yes
3 Jun 1, 2002 yes
4 Oct 1, 2003 no
4 Oct 2, 2003 yes
5 Oct 5, 2003 yes
6 Jun 2, 2000 no

The results should show:

Serial# Date
1 Sept 1, 2001
2 Oct 1, 2002
3 Jun 1, 2002

Your help is much appreciated. Thanks in advance, Chris.

 

[Michel Walsh]

Hi,


With one of the four methods in
http://www.mvps.org/access/queries/qry0020.htm you would probably be able to
get the latest record associated to each Serial#. Save that result as a
query, say qSerial. Next, make a last query based on qSerial to keep the
records WHERE Certification (or WHERE NOT Certification, if you want
those that are to be certified).


Hoping it may help,
Vanderghast, Access MVP