7 replies
First thing that comes to my mind is to make a view that makes: select distinct * from perseptions;
After that your query will group results from this view.
Bojdiar Alexandrov
Sorry I almost sleep...
Another way:
Select * from perceptions
Where (patient_id in (Select distinct patient_id from perseptions)) and
(date between date1 and date2)
Group by perceptions
by john_wills
This does not work because patient_id is *always* in (select distinct patient_id...). What is needed is something like "select distinct patient_id with a particular medication".
As for the view, I am not sure what you mean, but "select distinct * " will give me every prescription, as the dates are distinct.
Sorry, I have'nt understand your question first time.
First let me know that I understand. I have maked on Access a table "prescriprion" with data:
patient_id;prescription_number;date_begin;date_end
1;1;"rest";23.1.2002;24.1.2002
2;1;"rest";23.1.2002;26.1.2002
1;2;"sleep";25.1.2002;26.1.2002
1;3;"rest";27.1.2002;28.1.2002
---
If you make a query like:
SELECT medication, Count(*) AS patient_count
FROM prescription
WHERE date_begin>DateValue('1/1/2001') And date_endDateValue('1.1.2000') and date_end
by john_wills
by john_wills
What Bojidar Alexandrov has suggested is what I had, by first writing a solution in an algorithmic language, in the meantime worked out: SELECT DISTINCT medication, BISNumber
FROM Orders
WHERE DateOverlap([Forms]![Clients per Medication].[EarliestDate],[Forms]![Clients per Medication].[Latest Date],[Orders].[startdate],[Orders].[enddate]); the actual counting gets done with the report's facilities.
There are two problems with this: firstly, the query is referring to variables in the form invoking the report invoking the query, which non-modularity limits re-use of the report; secondly, I do not see how to count both unduplicated patients and prescriptions, which is not in my original question, but which my user may want.
by john_wills
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending