multiple dates but within 24 hrs

[Baldy]

Hi all,

just came across a reall tricky one, perhaps some smart cookie out there can
help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended them.

Now the problem is I want to find all the people that have the date/time
within 24 hours for patients that have been attended more than once.

now a simple duplicates query filters the single patients but then if I have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate then
query all the duplicates and cycle around to find any <24 datediff) but if
you think about this it gets mathematically complex very quickly with many
duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!

[Allen Browne]

To return the records where a UR has more than one record in 24 hours, you
could use a subquery such as this:

NearestRec:
(SELECT TOP 1 DT
FROM Table1 AS Dupe
WHERE (Dupe.UR = Table1.UR)
AND (DateDiff("n", Table1.DT, Table2.DT) < 1440)
AND (Dupe.ID <> Table1.ID)
ORDER BY Abs(Table1.DT - Dupe.DT), Dupe.ID)

That assumes:
- the table is named Table1;
- the date/time field is named DT.
- the primary key is named ID.

The DateDiff() limits it to those that have another record within 1440
minutes.
Sorting by the absolute value of the difference chooses the closest match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Hi all,

just came across a reall tricky one, perhaps some smart cookie out there
can help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended them.

Now the problem is I want to find all the people that have the date/time
within 24 hours for patients that have been attended more than once.

now a simple duplicates query filters the single patients but then if I
have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate
then query all the duplicates and cycle around to find any <24 datediff)
but if you think about this it gets mathematically complex very quickly
with many duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!

[Baldy]

Thanks for that I'll try it now!

Al

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

To return the records where a UR has more than one record in 24 hours, you
could use a subquery such as this:

NearestRec:
(SELECT TOP 1 DT
FROM Table1 AS Dupe
WHERE (Dupe.UR = Table1.UR)
AND (DateDiff("n", Table1.DT, Table2.DT) < 1440)
AND (Dupe.ID <> Table1.ID)
ORDER BY Abs(Table1.DT - Dupe.DT), Dupe.ID)

That assumes:
- the table is named Table1;
- the date/time field is named DT.
- the primary key is named ID.

The DateDiff() limits it to those that have another record within 1440
minutes.
Sorting by the absolute value of the difference chooses the closest match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Hi all,

just came across a reall tricky one, perhaps some smart cookie out there
can help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended
them.

Now the problem is I want to find all the people that have the date/time
within 24 hours for patients that have been attended more than once.

now a simple duplicates query filters the single patients but then if I
have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate
then query all the duplicates and cycle around to find any <24 datediff)
but if you think about this it gets mathematically complex very quickly
with many duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!

[Baldy]

Sorry call me stupid but I can't seem to work it out.

I tried something like this

select * from qry_MET_PATIENT_no_null_time_date,
(SELECT TOP 1 [Date of MET]
FROM qry_MET_PATIENT_duplicates AS Dupe
WHERE (Dupe.[UR Field] = qry_MET_PATIENT_no_null_time_date.UR)
AND (DateDiff("n", qry_MET_PATIENT_no_null_time_date.[Date of MET],
Table2.[Date of MET]) < 1440)
AND (Dupe.[UR Field] <> qry_MET_PATIENT_no_null_time_date.UR)
)

at I missing the point here?

Your help is greatly appreciated
Al


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

To return the records where a UR has more than one record in 24 hours, you
could use a subquery such as this:

NearestRec:
(SELECT TOP 1 DT
FROM Table1 AS Dupe
WHERE (Dupe.UR = Table1.UR)
AND (DateDiff("n", Table1.DT, Table2.DT) < 1440)
AND (Dupe.ID <> Table1.ID)
ORDER BY Abs(Table1.DT - Dupe.DT), Dupe.ID)

That assumes:
- the table is named Table1;
- the date/time field is named DT.
- the primary key is named ID.

The DateDiff() limits it to those that have another record within 1440
minutes.
Sorting by the absolute value of the difference chooses the closest match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Hi all,

just came across a reall tricky one, perhaps some smart cookie out there
can help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended
them.

Now the problem is I want to find all the people that have the date/time
within 24 hours for patients that have been attended more than once.

now a simple duplicates query filters the single patients but then if I
have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate
then query all the duplicates and cycle around to find any <24 datediff)
but if you think about this it gets mathematically complex very quickly
with many duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!

[Allen Browne]

Not exactly sure of your fields and matching, but did you have an ORDER BY
clause?

Sorting by the absolute difference of the date/time field and then choosing
the TOP 1 gives you the record that is the nearest match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Sorry call me stupid but I can't seem to work it out.

I tried something like this

select * from qry_MET_PATIENT_no_null_time_date,
(SELECT TOP 1 [Date of MET]
FROM qry_MET_PATIENT_duplicates AS Dupe
WHERE (Dupe.[UR Field] = qry_MET_PATIENT_no_null_time_date.UR)
AND (DateDiff("n", qry_MET_PATIENT_no_null_time_date.[Date of MET],
Table2.[Date of MET]) < 1440)
AND (Dupe.[UR Field] <> qry_MET_PATIENT_no_null_time_date.UR)
)

at I missing the point here?

Your help is greatly appreciated
Al
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

To return the records where a UR has more than one record in 24 hours,
you could use a subquery such as this:

NearestRec:
(SELECT TOP 1 DT
FROM Table1 AS Dupe
WHERE (Dupe.UR = Table1.UR)
AND (DateDiff("n", Table1.DT, Table2.DT) < 1440)
AND (Dupe.ID <> Table1.ID)
ORDER BY Abs(Table1.DT - Dupe.DT), Dupe.ID)

That assumes:
- the table is named Table1;
- the date/time field is named DT.
- the primary key is named ID.

The DateDiff() limits it to those that have another record within 1440
minutes.
Sorting by the absolute value of the difference chooses the closest
match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Hi all,

just came across a reall tricky one, perhaps some smart cookie out there
can help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended
them.

Now the problem is I want to find all the people that have the date/time
within 24 hours for patients that have been attended more than once.

now a simple duplicates query filters the single patients but then if I
have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate
then query all the duplicates and cycle around to find any <24 datediff)
but if you think about this it gets mathematically complex very quickly
with many duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!

[Baldy]

Yes I tried but I think I'm missing the point with the subquery.

I'll go and do some reading about it as I'm obviusly not great on my SQL.

Thanks for your help

Al
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Not exactly sure of your fields and matching, but did you have an ORDER BY
clause?

Sorting by the absolute difference of the date/time field and then
choosing the TOP 1 gives you the record that is the nearest match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .

Sorry call me stupid but I can't seem to work it out.

I tried something like this

select * from qry_MET_PATIENT_no_null_time_date,
(SELECT TOP 1 [Date of MET]
FROM qry_MET_PATIENT_duplicates AS Dupe
WHERE (Dupe.[UR Field] = qry_MET_PATIENT_no_null_time_date.UR)
AND (DateDiff("n", qry_MET_PATIENT_no_null_time_date.[Date of
MET], Table2.[Date of MET]) < 1440)
AND (Dupe.[UR Field] <> qry_MET_PATIENT_no_null_time_date.UR)
)

at I missing the point here?

Your help is greatly appreciated
Al
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

To return the records where a UR has more than one record in 24 hours,
you could use a subquery such as this:

NearestRec:
(SELECT TOP 1 DT
FROM Table1 AS Dupe
WHERE (Dupe.UR = Table1.UR)
AND (DateDiff("n", Table1.DT, Table2.DT) < 1440)
AND (Dupe.ID <> Table1.ID)
ORDER BY Abs(Table1.DT - Dupe.DT), Dupe.ID)

That assumes:
- the table is named Table1;
- the date/time field is named DT.
- the primary key is named ID.

The DateDiff() limits it to those that have another record within 1440
minutes.
Sorting by the absolute value of the difference chooses the closest
match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Baldy" <Jo**@smith.com> wrote in message
news:41**********@news.adelaide.pipenetworks.com.. .
Hi all,

just came across a reall tricky one, perhaps some smart cookie out
there can help.

I'm querying patient data from our hospital it might look like this

UR Date arrival time
429784 27/01/2004 16:04
421685 25/07/2003 19:35
421685 24/07/2003 12:47
427670 23/07/2004 19:45
430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30
431563 13/06/2004 14:16
432121 12/08/2004 19:48

(sorry in Australia our date are dd/mm/yyyy )
first column is the patient ID (UR) (de-identified so not a real UR for
here) the second is the date & time that the emergency team attended
them.

Now the problem is I want to find all the people that have the
date/time within 24 hours for patients that have been attended more
than once.

now a simple duplicates query filters the single patients but then if I
have

430163 4/03/2004 17:25
430163 20/02/2004 22:30
430163 20/02/2004 2:16
430163 5/03/2004 20:30

clears I can see that this person has been attended 4 times and 2 times
within 24 hours.

BUT! how do I simply query this without going mad! I could write a VBA
function to do a comparison for each duplicate (ie find each duplicate
then query all the duplicates and cycle around to find any <24
datediff) but if you think about this it gets mathematically complex
very quickly with many duplicates ( O^n maybe?)

Can anyone come up with a more simple solution maybe.

A good homework question perhaps?

Baldy!