Find duplicates in last 48 hours

[Doc94]

I am working on a duplicates query. Some data points I am using are the date and say medical record number. I am looking to run a duplicates query between certain dates. That part is easy. But I want the records to be returned only if it is a duplicate in the last 48 hours. So for instance if the record was generated yesterday (4/2/05) and a new record with the same medical record number was generated WITHIN 48 hours prior or 48 hours after, it would be included. But no earlier or later.Thanks if anyone has any ideas.

 

[PHV]

Can you please post your actual SQL code finding the duplicates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Doc94]

Here is the SQL code:

PARAMETERS [date 1] DateTime, [date 2] DateTime;
SELECT Detail.[medrec#], Detail.HospitalArrivalTime, Detail.ArrivalDate
FROM Detail
WHERE (((Detail.[medrec#]) In (SELECT [medrec#] FROM [Detail] As Tmp GROUP BY [medrec#] HAVING Count(*)>1 )) AND ((Detail.HospitalArrivalTime) Between [date 1] And [date 2]+1))
ORDER BY Detail.[medrec#];

I know I could run the report for a two day period and a three day period and find the 48 hour answer but would like to run it for a month.
And the reasoning behind it is we look at repeat visits to the ER with in 48 hours to review the charts as a quality control measure.

 

[PHV]

You may try something like this:
PARAMETERS [date 1] DateTime, [date 2] DateTime;
SELECT A.[medrec#], A.HospitalArrivalTime, A.ArrivalDate
FROM Detail AS A
WHERE A.HospitalArrivalTime Between [date 1] And [date 2]+1
AND EXISTS (SELECT * FROM Detail AS B
WHERE B.[medrec#]=A.[medrec#] AND B.HospitalArrivalTime<>A.HospitalArrivalTime
AND B.HospitalArrivalTime Between A.HospitalArrivalTime-2 And A.HospitalArrivalTime+2)
ORDER BY A.[medrec#];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Doc94]

PH-
That is absolutely fantastic! Works like a charm. I have been stuck in a rut of thinking about how to do it- your method is completely different than the way I was thinking about doing it.
Thanks a bunch.

 

[Doc94]

PH-
One other question- why do I end up with a table labled "A" in the query but the table is not in my database but has the exact same data as my detail table. I assume it is a temporary table drawing the data from "Detail"?

 

[PHV]

A is an alias defined here:
FROM Detail AS A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

The table names A and B are what are called aliases.

Because you are running a nested query (a query within a query) and using criteria that could be ambiguous with both queries using the same table, the aliases are used to clearly identify which instance of the table is being referred to.

Since B is the alias for your subquery and A the alias for the main SELECT statement, the returned table will be called A.

I hope this makes sense. I know what I meant, but I'm not sure that I've explained it very well.

 

[earthandfire]

Sorry PHV, we seem to be overlapping tonight!!

 

[PHV]

Sorry PHV, we seem to be overlapping tonight
No problem, there is no proprietary thread.

 

[earthandfire]

 

[Doc94]

That makes sense. What other instances would using an alias be useful? The following is a query I use to look at times. It is a long drawn out query that takes forever to run.
PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT Detail.[ED#], Detail.HospitalArrivalTime, TimeValue([HospitalArrivalTime]) AS HATTime, Detail.DispositionTime, IIf(([DispositionTime]-[HATTime])<0,(([DispositionTime]-[HATTime])+1)*1440,([DispositionTime]-[HATTime])*1440) AS LOS, Detail.TriageDateTime, TimeValue([TriageDateTime]) AS TTime, Detail.PhysicianExamTime, IIf(([PhysicianExamTime]-[TTime])<0,(([PhysicianExamTime]-[TTime])+1)*1440,([PhysicianExamTime]-[TTime])*1440) AS TToMD, IIf(([PhysicianExamTime]-[HATTime])<0,(([PhysicianExamTime]-[HATTime])+1)*1440,([PhysicianExamTime]-[HATTime])*1440) AS HATTimeoMD, IIf([DispositionTime]-[PhysicianExamTime]<0,([DispositionTime]-[PhysicianExamTime]+1)*1440,([DispositionTime]-[PhysicianExamTime])*1440) AS MDToDT, [Enter Beginning Date] AS Beg, [Enter Ending Date] AS [End], IIf(([EDBedTime]-[HATTime])<0,(([EDBedTime]-[HATTime])+1)*1440,([EdBedTime]-[HATTime])*1440) AS HATTimeoEDBT, IIf(([TTime]-[HATTime])<0,(([TTime]-[HATTime])+1)*1440,([TTime]-[HATTime])*1440) AS HAToTT, IIf([RegistrationTime]-[TTime]<0,([RegistrationTime]-[TTime]+1)*0,([RegistrationTime]-[TTime])*1440) AS TToRegT, IIf(([EDBedTime]-[RegistrationTime])<0,(([EDBedTime]-[RegistrationTime])+1)*1440,([EDBedTime]-[RegistrationTime])*1440) AS RToEDBT, IIf([PhysicianExamTime]-[EDBedTime]<0,([PhysicianExamTime]-[EDBedTime]+1)*1440,([PhysicianExamTime]-[EDBedTime])*1440) AS EDBToMDT, DateValue([Detail]![HospitalArrivalTime]) AS HAD, IIf([DispositionTime]-[DispositionOrderTime]<0,([DispositionTime]-[DispositionOrderTime]+1)*1440,([DispositionTime]-[DispositionOrderTime])*1440) AS DOTToDT, Detail.EDBedTime, Detail.RegistrationTime, Detail.DispositionOrderTime, Detail.DispositionCode, Detail.Lastname, Detail.Firstname, Detail.TSheetTitle
FROM Detail
WHERE (((Detail.HospitalArrivalTime) Between [Enter Beginning Date] And ([Enter Ending Date]+1)));

Thanks

 

[jimirvine]

Hi,
Quite a statement there
I'm assuming that the 'Time' fields are actually date fields with times, and that the calculation is trying to work out the number of minutes between the two dates?
Rather than using basic arithmetic, use the in built Access functions designed to deal with dates. i.e. DateDiff and DateAdd. They will process faster. Sub queries do not improve the speed of queries, they simply allow you to interrogate tables in a more complex way that cannot be done by a single SELECT. point is, if it can be done in a single query 99 times out of a hundred it will be faster than attempting a sub query (aka in-line query)
Note that you can alter the parameters of DateDiff and DateAdd to request specific intervals (such as "How many minutes, days, months, years" between two date values) which would also get rid of your *1440.

HTH
Jim

 

[Doc94]

Jimirvine-
Thanks that is useful information. Most of the calculations are time related not date related. I do not see the TimeDiff as a built in function- will it still work?
One problem I am having with the speed of the database is the backend has been moved to a different server. safer there but slower. I have been told that changing it to SQL instead of the jet would speed it up. Not a task I am up for with my limited knowledge.

 

[Doc94]

One other thing- the query was written in Access using the design view and not the SQL view. I cannot take credit that my SQL writing is anywhere near that good.

 

[jimirvine]

LOL, DateDiff will still work with times, it's just a case of setting the 'Interval' parameter to "n" for minutes or "s" for seconds.
HTH
Jim