Discussion Locked

counting distinct values in Access

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a table of prescriptions, indexed by patient number and a running number for each patient. The row includes medication name, start date and end date. I want a report counting the number of patients getting each kind of medication within a given time period. The user selects the date range in an Access form, which opens a report with a criterion using a call to my date overlap function. The report is grouped by medication, with the detail empty and the group footer showing medication nameand count of patients. This works... except that sometimes a patient has a medication repeated within the time period the user specifies, and then I find myself counting prescriptions, not patients. I have tried various SQL tricks in place of havingmerely the table as data source. I cannot SELECT DISTINCT because OpenForm needs the dates for its own criterion. I am willing to change the report's internal format.

7 replies

by bojidar alexandrov

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

by bojidar 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.

by bojidar alexandrov

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

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.

This discussion is closed to new replies.