Modifying duplicates query?

[tintin]

I have a table with various fields, two of which are "date" and "serial
number". I'm trying to construct a query which will return all records
unless there are multiple matches in the "serial number" field , in
which case the following should happen;
duplicate "serial number" entries should be checked to see if they fall
within 5 days of each other as defined by the "date" field. If so, the
newer record should be discounted from the query but not deleted from
the table.

Given the table
serial number date other fields
123 01/01/01 xxxxx
123 07/01/01 yyyyy
123 08/01/01 zzzzz

the query result should be
serial number date other fields
123 01/01/01 xxxxx
123 07/01/01 yyyyy

Can anyone help with this?

 

[Michel Walsh]

Hi,



Assuming the sequence
5, 3, 1.

The result should display only 5. But now, with the sequence
9, 5, 3, 1

is the result only to display 9? or 9 and 3 ? Note that if is should
display 9 and 3, without the 5, some "retracing", in history, become a
problem.

Assuming it has to display only 9 (ie, display the lead of an
un-interrupted sequences, and a sequence is said not interrupted if the gap
is not more than by 5 units (here, days) or less, with some other member of
the sequence. ie,

18, 9, 5, 3, 1

would display 18 and 9 but not 5 (gap between 9 and 5 less than 5), neither
3 (gap with a member of the sequence {9, 5}, namely 5, is less than 5
units), the following, untested, should do:


SELECT a.SerialNumber, a.date, LAST(otherField1), LAST(otherField2)

FROM myTable As b RIGTH JOIN myTable As a
ON ( b.SerialNumber = a.SerialNumber AND b.date > a.date)

GROUP BY a.SerialNumber, a.date

HAVING MIN(b.date)-a.date >=5





Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Well, definitively, change the Having to something like this:


HAVING Nz(MIN(b.date)-a.date >=5, True)



or else the most recent lead would not be listed. Indeed, in those cases,
MIN(b.date) returns a NULL, so the Nz is required to change that NULL into
a True.


Hoping it may help,
Vanderghast, Access MVP

Hi,



Assuming the sequence
5, 3, 1.

The result should display only 5. But now, with the sequence
9, 5, 3, 1

is the result only to display 9? or 9 and 3 ? Note that if is should
display 9 and 3, without the 5, some "retracing", in history, become a
problem.

Assuming it has to display only 9 (ie, display the lead of an
un-interrupted sequences, and a sequence is said not interrupted if the gap
is not more than by 5 units (here, days) or less, with some other member of
the sequence. ie,

18, 9, 5, 3, 1

would display 18 and 9 but not 5 (gap between 9 and 5 less than 5), neither
3 (gap with a member of the sequence {9, 5}, namely 5, is less than 5
units), the following, untested, should do:


SELECT a.SerialNumber, a.date, LAST(otherField1), LAST(otherField2)

FROM myTable As b RIGTH JOIN myTable As a
ON ( b.SerialNumber = a.SerialNumber AND b.date > a.date)

GROUP BY a.SerialNumber, a.date

HAVING MIN(b.date)-a.date >=5





Hoping it may help,
Vanderghast, Access MVP