Query results showing duplicate values

  • Thread starter Thread starter Chris Murphy
  • Start date Start date
C

Chris Murphy

Hi everyone, I need help please. I have a table with
numerous, duplicate records. One field in the table shows
date and one shows serial number. The serial number is
duplicated many times, however the date will change. What
I am trying to do is filter the records to show the serial
numbers that have a "date < current date - 275 days".
Effectively showing records that are less than 3 months
away. There are 2 problems with what I have done. 1) It
shows every record that has a "date < current date - 275",
not just the most recent date and 2) If there is a "date >
current date - 275", then I don't want any instances of
that serial number to occur at all. (ie. if "current date
= Nov 1, 2003", then "date - 275 = Feb 1, 2003". If I
have 2 records with dates "Dec 1, 2002" and "Sept 1,
2003", then it shows "Dec 1, 2002" record. In this case,
since "Sept 1, 2003 > Feb 1, 2003", I don't want any
records to show.) Anyways, this may be quite confusing,
but not as confused as myself. Any suggestions welcome.
Thanks in advance.
 
Yes, your description of what you want is confusing.

First, call your datefield something besides date (I've used ActionDate). It
makes life less confusing for the computer and the human.

Second, 3 months is not 275 days, it is roughly 90 days.

ActionDate < Date() - 275 would give you ActionDates that occurred more than 9
months ago. Is that what you want


Get the latest ActionDate for all the records and save that as query one

SELECT SerialNumber, Max(ActionDate) as LatestAction
FROM TableName
Group By SerialNumber

Now in a second query:
SELECT T.SerialNumber, Max(T.ActionDate) as NewLatest
FROM TableName as T INNER JOIN QryOne As Q
ON T.SerialNumber = Q.SerialNumber
WHERE Q.LatestAction < DateAdd("m",-9,Date())
AND T.ActionDate < DateAdd("m",-9,Date())
GROUP BY T.SerialNumber

This can be done in a single query, but that is a bit more complex to do.
 
Back
Top