434,640 Members | 2,118 Online
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

# multiple dates but within 24 hrs

 P: n/a 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! Nov 13 '05 #1
5 Replies

 P: n/a 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" 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! Nov 13 '05 #2

 P: n/a Thanks for that I'll try it now! Al "Allen Browne" 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" 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! Nov 13 '05 #3

 P: n/a 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" 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" 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! Nov 13 '05 #4