Not a complete answer but some advice:
Apparently the Date_Stopped field allows zero-length strings. Is it possible the field might be null?
SELECT person_id, modify_timestamp, medication_name
FROM dbo_patient_medication
WHERE ((date_stopped="" Or date_stopped >"20150101") AND gcn_seqno=2379) OR gcn_seqno IN (2380, 2381, 2389, 2390, 2391, 2394, 2395, 2396, 3100, 3101, 3102, 6416, 6460, 6461, 11743, 13675, 16310, 16366, 16367, 16576, 16577, 16578, 16579, 20741, 21694, 22344, 27864, 29967, 29968, 29969, 33364, 33366, 40238, 45772, 46172, 46757, 48571, 49758, 50556, 50557, 50558, 51214, 51784, 51785, 51786, 52944, 53689, 53690, 53691, 53693, 53694, 53695, 57859, 57863, 57865, 58432, 58486, 61199, 61230, 62885, 63025, 63683, 64063, 64310, 64676, 64677, 65929, 66297, 66349, 66350);
Do you get any results if you build a query like:
SELECT person_id, modify_timestamp, Count(*) as NumOf
FROM dbo_patient_medication
WHERE ((date_stopped="" Or date_stopped >"20150101") AND gcn_seqno=2379) OR gcn_seqno IN (2380, 2381, 2389, 2390, 2391, 2394, 2395, 2396, 3100, 3101, 3102, 6416, 6460, 6461, 11743, 13675, 16310, 16366, 16367, 16576, 16577, 16578, 16579, 20741, 21694, 22344, 27864, 29967, 29968, 29969, 33364, 33366, 40238, 45772, 46172, 46757, 48571, 49758, 50556, 50557, 50558, 51214, 51784, 51785, 51786, 52944, 53689, 53690, 53691, 53693, 53694, 53695, 57859, 57863, 57865, 58432, 58486, 61199, 61230, 62885, 63025, 63683, 64063, 64310, 64676, 64677, 65929, 66297, 66349, 66350)
GROUP BY person_id, modify_timestamp
HAVING Count(*) > 1;
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 25 Aug 2015 13:07:39 -0700
Subject: [MS_AccessPros] trouble with query- some patients showing info more than once
I am trying to ultimately get a list of the last time a statin has been prescribed for a patient
I have a patient medication table that I have been able to write a query that then shows all the statins with person_id. that are active
after that I have limited the fields only to patient_ID, modify_timestamp and Medication-name
but 3 patients keep showing up 2 times.
I thought maybe it was the create timestamp field that caused the problem, but that was not it, removed all number fields except id and modify timestamp and still these 3 patients show up in the final query.
so if someone could please look at these three and see what I have done that could be causing this problem
1st query is the main one to identify statin medications currently active
(unfortunately the pharmaceauticals cant pick one number)
SELECT dbo_patient_medication.person_id, dbo_patient_medication.modify_timestamp, dbo_patient_medication.medication_name
FROM dbo_patient_medication
WHERE (((dbo_patient_medication.date_stopped)="" Or (dbo_patient_medication.date_stopped)>"20150101") AND ((dbo_patient_medication.gcn_seqno)=2379)) OR (((dbo_patient_medication.gcn_seqno)=2380 Or (dbo_patient_medication.gcn_seqno)=2381 Or (dbo_patient_medication.gcn_seqno)=2389 Or (dbo_patient_medication.gcn_seqno)=2390 Or (dbo_patient_medication.gcn_seqno)=2391 Or (dbo_patient_medication.gcn_seqno)=2394 Or (dbo_patient_medication.gcn_seqno)=2395 Or (dbo_patient_medication.gcn_seqno)=2396 Or (dbo_patient_medication.gcn_seqno)=3100 Or (dbo_patient_medication.gcn_seqno)=3101 Or (dbo_patient_medication.gcn_seqno)=3102 Or (dbo_patient_medication.gcn_seqno)=6416 Or (dbo_patient_medication.gcn_seqno)=6460 Or (dbo_patient_medication.gcn_seqno)=6461 Or (dbo_patient_medication.gcn_seqno)=11743 Or (dbo_patient_medication.gcn_seqno)=13675 Or (dbo_patient_medication.gcn_seqno)=16310 Or (dbo_patient_medication.gcn_seqno)=16366 Or (dbo_patient_medication.gcn_seqno)=16367 Or (dbo_patient_medication.gcn_seqno)=16576 Or (dbo_patient_medication.gcn_seqno)=16577 Or (dbo_patient_medication.gcn_seqno)=16578 Or (dbo_patient_medication.gcn_seqno)=16579 Or (dbo_patient_medication.gcn_seqno)=20741 Or (dbo_patient_medication.gcn_seqno)=21694 Or (dbo_patient_medication.gcn_seqno)=22344 Or (dbo_patient_medication.gcn_seqno)=27864 Or (dbo_patient_medication.gcn_seqno)=29967 Or (dbo_patient_medication.gcn_seqno)=29968 Or (dbo_patient_medication.gcn_seqno)=29969 Or (dbo_patient_medication.gcn_seqno)=33364 Or (dbo_patient_medication.gcn_seqno)=33366 Or (dbo_patient_medication.gcn_seqno)=40238 Or (dbo_patient_medication.gcn_seqno)=45772 Or (dbo_patient_medication.gcn_seqno)=46172 Or (dbo_patient_medication.gcn_seqno)=46757 Or (dbo_patient_medication.gcn_seqno)=48571 Or (dbo_patient_medication.gcn_seqno)=49758 Or (dbo_patient_medication.gcn_seqno)=50556 Or (dbo_patient_medication.gcn_seqno)=50557 Or (dbo_patient_medication.gcn_seqno)=50558 Or (dbo_patient_medication.gcn_seqno)=51214 Or (dbo_patient_medication.gcn_seqno)=51784 Or (dbo_patient_medication.gcn_seqno)=51785 Or (dbo_patient_medication.gcn_seqno)=51786 Or (dbo_patient_medication.gcn_seqno)=52944 Or (dbo_patient_medication.gcn_seqno)=53689 Or (dbo_patient_medication.gcn_seqno)=53690 Or (dbo_patient_medication.gcn_seqno)=53691 Or (dbo_patient_medication.gcn_seqno)=53693 Or (dbo_patient_medication.gcn_seqno)=53694 Or (dbo_patient_medication.gcn_seqno)=53695 Or (dbo_patient_medication.gcn_seqno)=57859 Or (dbo_patient_medication.gcn_seqno)=57863 Or (dbo_patient_medication.gcn_seqno)=57865 Or (dbo_patient_medication.gcn_seqno)=58432 Or (dbo_patient_medication.gcn_seqno)=58486 Or (dbo_patient_medication.gcn_seqno)=61199 Or (dbo_patient_medication.gcn_seqno)=61230 Or (dbo_patient_medication.gcn_seqno)=62885 Or (dbo_patient_medication.gcn_seqno)=63025 Or (dbo_patient_medication.gcn_seqno)=63683 Or (dbo_patient_medication.gcn_seqno)=64063 Or (dbo_patient_medication.gcn_seqno)=64310 Or (dbo_patient_medication.gcn_seqno)=64676 Or (dbo_patient_medication.gcn_seqno)=64677 Or (dbo_patient_medication.gcn_seqno)=65929 Or (dbo_patient_medication.gcn_seqno)=66297 Or (dbo_patient_medication.gcn_seqno)=66349 Or (dbo_patient_medication.gcn_seqno)=66350));
second query limits the first query to show the last time a patient got any statin but I am getting some patients showing up more than 1 time
SELECT statin_query_all.*
FROM statin_query_all INNER JOIN (SELECT person_id, MAX(modify_timestamp) AS TimeMatch FROM statin_query GROUP BY person_id) AS MaxRecord ON (statin_query_all.person_id = MaxRecord.person_id) AND (statin_query_all.modify_timestamp = MaxRecord.TimeMatch); (5 patients showing up two times)
I have used the second query with other queries and have not had this problem so I am just not sure what I am doing wrong.
thank you
Patty
- All of the hard-coded GCN_seqno values should be identified in some table as statins (or whatever). Keeping a list like this in an expression isn't ideal
- You can use the IN () clause to simplify your first query
Apparently the Date_Stopped field allows zero-length strings. Is it possible the field might be null?
SELECT person_id, modify_timestamp, medication_name
FROM dbo_patient_medication
WHERE ((date_stopped="" Or date_stopped >"20150101") AND gcn_seqno=2379) OR gcn_seqno IN (2380, 2381, 2389, 2390, 2391, 2394, 2395, 2396, 3100, 3101, 3102, 6416, 6460, 6461, 11743, 13675, 16310, 16366, 16367, 16576, 16577, 16578, 16579, 20741, 21694, 22344, 27864, 29967, 29968, 29969, 33364, 33366, 40238, 45772, 46172, 46757, 48571, 49758, 50556, 50557, 50558, 51214, 51784, 51785, 51786, 52944, 53689, 53690, 53691, 53693, 53694, 53695, 57859, 57863, 57865, 58432, 58486, 61199, 61230, 62885, 63025, 63683, 64063, 64310, 64676, 64677, 65929, 66297, 66349, 66350);
Do you get any results if you build a query like:
SELECT person_id, modify_timestamp, Count(*) as NumOf
FROM dbo_patient_medication
WHERE ((date_stopped="" Or date_stopped >"20150101") AND gcn_seqno=2379) OR gcn_seqno IN (2380, 2381, 2389, 2390, 2391, 2394, 2395, 2396, 3100, 3101, 3102, 6416, 6460, 6461, 11743, 13675, 16310, 16366, 16367, 16576, 16577, 16578, 16579, 20741, 21694, 22344, 27864, 29967, 29968, 29969, 33364, 33366, 40238, 45772, 46172, 46757, 48571, 49758, 50556, 50557, 50558, 51214, 51784, 51785, 51786, 52944, 53689, 53690, 53691, 53693, 53694, 53695, 57859, 57863, 57865, 58432, 58486, 61199, 61230, 62885, 63025, 63683, 64063, 64310, 64676, 64677, 65929, 66297, 66349, 66350)
GROUP BY person_id, modify_timestamp
HAVING Count(*) > 1;
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 25 Aug 2015 13:07:39 -0700
Subject: [MS_AccessPros] trouble with query- some patients showing info more than once
I am trying to ultimately get a list of the last time a statin has been prescribed for a patient
I have a patient medication table that I have been able to write a query that then shows all the statins with person_id. that are active
after that I have limited the fields only to patient_ID, modify_timestamp and Medication-name
but 3 patients keep showing up 2 times.
I thought maybe it was the create timestamp field that caused the problem, but that was not it, removed all number fields except id and modify timestamp and still these 3 patients show up in the final query.
so if someone could please look at these three and see what I have done that could be causing this problem
1st query is the main one to identify statin medications currently active
(unfortunately the pharmaceauticals cant pick one number)
SELECT dbo_patient_medication.person_id, dbo_patient_medication.modify_timestamp, dbo_patient_medication.medication_name
FROM dbo_patient_medication
WHERE (((dbo_patient_medication.date_stopped)="" Or (dbo_patient_medication.date_stopped)>"20150101") AND ((dbo_patient_medication.gcn_seqno)=2379)) OR (((dbo_patient_medication.gcn_seqno)=2380 Or (dbo_patient_medication.gcn_seqno)=2381 Or (dbo_patient_medication.gcn_seqno)=2389 Or (dbo_patient_medication.gcn_seqno)=2390 Or (dbo_patient_medication.gcn_seqno)=2391 Or (dbo_patient_medication.gcn_seqno)=2394 Or (dbo_patient_medication.gcn_seqno)=2395 Or (dbo_patient_medication.gcn_seqno)=2396 Or (dbo_patient_medication.gcn_seqno)=3100 Or (dbo_patient_medication.gcn_seqno)=3101 Or (dbo_patient_medication.gcn_seqno)=3102 Or (dbo_patient_medication.gcn_seqno)=6416 Or (dbo_patient_medication.gcn_seqno)=6460 Or (dbo_patient_medication.gcn_seqno)=6461 Or (dbo_patient_medication.gcn_seqno)=11743 Or (dbo_patient_medication.gcn_seqno)=13675 Or (dbo_patient_medication.gcn_seqno)=16310 Or (dbo_patient_medication.gcn_seqno)=16366 Or (dbo_patient_medication.gcn_seqno)=16367 Or (dbo_patient_medication.gcn_seqno)=16576 Or (dbo_patient_medication.gcn_seqno)=16577 Or (dbo_patient_medication.gcn_seqno)=16578 Or (dbo_patient_medication.gcn_seqno)=16579 Or (dbo_patient_medication.gcn_seqno)=20741 Or (dbo_patient_medication.gcn_seqno)=21694 Or (dbo_patient_medication.gcn_seqno)=22344 Or (dbo_patient_medication.gcn_seqno)=27864 Or (dbo_patient_medication.gcn_seqno)=29967 Or (dbo_patient_medication.gcn_seqno)=29968 Or (dbo_patient_medication.gcn_seqno)=29969 Or (dbo_patient_medication.gcn_seqno)=33364 Or (dbo_patient_medication.gcn_seqno)=33366 Or (dbo_patient_medication.gcn_seqno)=40238 Or (dbo_patient_medication.gcn_seqno)=45772 Or (dbo_patient_medication.gcn_seqno)=46172 Or (dbo_patient_medication.gcn_seqno)=46757 Or (dbo_patient_medication.gcn_seqno)=48571 Or (dbo_patient_medication.gcn_seqno)=49758 Or (dbo_patient_medication.gcn_seqno)=50556 Or (dbo_patient_medication.gcn_seqno)=50557 Or (dbo_patient_medication.gcn_seqno)=50558 Or (dbo_patient_medication.gcn_seqno)=51214 Or (dbo_patient_medication.gcn_seqno)=51784 Or (dbo_patient_medication.gcn_seqno)=51785 Or (dbo_patient_medication.gcn_seqno)=51786 Or (dbo_patient_medication.gcn_seqno)=52944 Or (dbo_patient_medication.gcn_seqno)=53689 Or (dbo_patient_medication.gcn_seqno)=53690 Or (dbo_patient_medication.gcn_seqno)=53691 Or (dbo_patient_medication.gcn_seqno)=53693 Or (dbo_patient_medication.gcn_seqno)=53694 Or (dbo_patient_medication.gcn_seqno)=53695 Or (dbo_patient_medication.gcn_seqno)=57859 Or (dbo_patient_medication.gcn_seqno)=57863 Or (dbo_patient_medication.gcn_seqno)=57865 Or (dbo_patient_medication.gcn_seqno)=58432 Or (dbo_patient_medication.gcn_seqno)=58486 Or (dbo_patient_medication.gcn_seqno)=61199 Or (dbo_patient_medication.gcn_seqno)=61230 Or (dbo_patient_medication.gcn_seqno)=62885 Or (dbo_patient_medication.gcn_seqno)=63025 Or (dbo_patient_medication.gcn_seqno)=63683 Or (dbo_patient_medication.gcn_seqno)=64063 Or (dbo_patient_medication.gcn_seqno)=64310 Or (dbo_patient_medication.gcn_seqno)=64676 Or (dbo_patient_medication.gcn_seqno)=64677 Or (dbo_patient_medication.gcn_seqno)=65929 Or (dbo_patient_medication.gcn_seqno)=66297 Or (dbo_patient_medication.gcn_seqno)=66349 Or (dbo_patient_medication.gcn_seqno)=66350));
second query limits the first query to show the last time a patient got any statin but I am getting some patients showing up more than 1 time
SELECT statin_query_all.*
FROM statin_query_all INNER JOIN (SELECT person_id, MAX(modify_timestamp) AS TimeMatch FROM statin_query GROUP BY person_id) AS MaxRecord ON (statin_query_all.person_id = MaxRecord.person_id) AND (statin_query_all.modify_timestamp = MaxRecord.TimeMatch); (5 patients showing up two times)
I have used the second query with other queries and have not had this problem so I am just not sure what I am doing wrong.
thank you
Patty
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar