trying to find duplicates within a 30 day time fram 1

[johnmac42]

have written a service management database. I want to create a report that will look up a serial number between 2 dates. Check to see if that serial number has previously been in for repair. If the s/n has been in previously check to see if the date shipped on the previous service order was shipped <= 30 days of the date received on the new service order. If so I would like it included in the report. I am not sure that this is the best way to proceed, but I have alreadyc reated a duplicates query that looks up any serial numbers between the start date and the end date. What I do not know is how to make the comparison between the records found, and enter it on a report.

Sample data

included on report
service order serial number daterec dateshipped
3 123456 02/01/07 02/14/07
2 123456 01/24/07 02/26/07
4 235666 03/01/07 03/05/07
5 235666 01/30/07 02/04/07

not on report
6 855122 03/01/07 4/08/07
(was only in for service 1 time)

 

[Crowley16]

use the IN clause, something like:

select
blah...
from
tables...
where
someID not in (
select someID
from tables...
where conditions...
)

--------------------
Procrastinate Now!

 

[Remou]

I am a little confused by your requirements. Have a look at this and see if it returns something close to what you want:

[tt]SELECT N.[serial number],
O.dateshipped,
N.daterec,
N.[daterec]-O.[dateshipped] AS Diff_Rec_Ship
FROM tblTable AS N
INNER JOIN tblTable AS O
ON N.[serial number] = O.[serial number]
WHERE N.daterec)>[O].[daterec][/tt]