Having Count(*)

[Steve S]

Hi:
I'm using the following code to return certain records having a count > then
a certain number. I want to be able to enter and define this "having count
(*)" on a form. Can anyone help me with this?
Thanks in advance,
Steve

SELECT Link, Patient_Last_Name, Patient_First_Name,
Patient_Street_Address, Birth_Date, Date_Rx_Written
FROM dbo.tblMainDatabase
WHERE (Patient_Last_Name IN
(SELECT [patient_last_name]
FROM [tblMainDatabase] AS Tmp
GROUP BY [patient_last_name],
[patient_first_name]
HAVING COUNT(*) >2)) AND (Date_Rx_Written
BETWEEN @enter_beginning_date AND @enter_ending_date)
ORDER BY Patient_Last_Name, Patient_First_Name

 

[Michel Walsh]

HI,

Since you are using an Aggregate (COUNT), any field in the SELECT clause
must either be also aggregated, either in the GROUP BY clause.


SELECT f1, MAX(f2)
FROM somewhere
GROUP BY f1


is ok, but


SELECT f1, f2
FROM somewhere
GROUP BY f1


is not, since f2 is neither in the GROUP BY, neither aggregated.


In your case, you don't use a GROUP BY, so all fields in the SELECT clause
must be aggregated.

Hoping it may help,
Vanderghast, Access MVP

 

[Dale Fye]

I think you may have some additional problems as well. Since I'm not
exactly sure what all information is in tblMainDatabase, this is just a
swag. But, the way you have your WHERE clause written is a little strange.

The way it reads now, what you are asking it to do is identify the last name
of any patient that has more than 2 records in the database. So if John Doe
has one record, and Jane Doe has 3 records, it will identify the Doe last
name, and your query will return anyone else with the last name of Doe who
was issued a perscription between your beginning and ending dates.

I'm going to assume that what you really want is to identify all the people
that were issued more than two perscriptions between your beginning and
ending dates. I will also assume that you do have a Patient_ID field,
although there is no evidence of this. If not, and you cannot figure out
how to modify what I've posted, post back and I'll send you some different
instructions. What you need to do is create another query that looks
something like:

Query1:
SELECT Patient_ID
FROM tblMainDataBase
WHERE [Date_Rx_Written] BETWEEN @enter_beginning_date AND @enter_ending_date
GROUP BY Patient_ID
HAVING Count(*) > 2

Now, to get the other information you want, create a new query:

SELECT T.Link, T.Patient_Last_Name, T.Patient_First_Name,
T.Patient_Street_Address, T.Birth_Date, T.Date_Rx_Written
FROM dbo.tblMainDatabase as T
INNER JOIN Query1 as Q ON T.Patient_ID = Q.Patient_ID

If you don't have a Patient_ID, then you really need to get one. At any
rate, replace the reference to Patient_ID with both Patient_Last_Name, and
Patient_First_Name in Query1 and then modify the Join in Query 2 to use both
the first and last names.

HTH
Dale

 

[Steve S]

Thanks for your response to my 'having count >' post. The problem I'm
having is that there is and
can be no patient ID field. I'm uploading information from facilities
statewide on a daily basis in a specified format. Because of HIPPA
regulations SS#s can't be used as patient IDs. There is a patient
identifier field within each record, but the contents of this field are
inconsistent between pharmacies in that some may use DL#s, some make up
thier own number, some leave the field blank, etc....I'll be uploading about
1.5million records into the database per month, so assigning a patient id on
my end is impossible. My plans are to run a threshold query based on the
query below to determine duplicate patients w/ multiple prescriptions. I
realize that
it will return more than one patient per query, but these are my plans: I
want to enable the user to adust the 'having count(*)># on a form so that
the threshold can be changed on the user form level rather than having to
open the query to make the change (in other words, if the having count(*)>2
yields too many returns I want to be able to raise that "having count(*)>#"
threshold on the form). To uniquely identify patients, my plans, unless
there is a better way is to use a blank 'link field' to mark single patients
with more that the 'having count(*)>"threshold#" of prescriptions. Run the
query below, mark duplicate patients with a unique identifier (like "X") and
run a second parameter query based on the previously marked "X" fields.
Once I'm done I'll run an update query to delete the link. The main thing
I'm shooting for now is to enable change of the threshold on the form level.
I hope this all makes sense and any help is greatly appreciated.

Thanks a million,
Steve

I think you may have some additional problems as well. Since I'm not
exactly sure what all information is in tblMainDatabase, this is just a
swag. But, the way you have your WHERE clause written is a little
strange.

The way it reads now, what you are asking it to do is identify the last
name
of any patient that has more than 2 records in the database. So if John
Doe
has one record, and Jane Doe has 3 records, it will identify the Doe last
name, and your query will return anyone else with the last name of Doe
who
was issued a perscription between your beginning and ending dates.

I'm going to assume that what you really want is to identify all the
people
that were issued more than two perscriptions between your beginning and
ending dates. I will also assume that you do have a Patient_ID field,
although there is no evidence of this. If not, and you cannot figure out
how to modify what I've posted, post back and I'll send you some different
instructions. What you need to do is create another query that looks
something like:

Query1:
SELECT Patient_ID
FROM tblMainDataBase
WHERE [Date_Rx_Written] BETWEEN @enter_beginning_date AND
@enter_ending_date
GROUP BY Patient_ID
HAVING Count(*) > 2

Now, to get the other information you want, create a new query:

SELECT T.Link, T.Patient_Last_Name, T.Patient_First_Name,
T.Patient_Street_Address, T.Birth_Date, T.Date_Rx_Written
FROM dbo.tblMainDatabase as T
INNER JOIN Query1 as Q ON T.Patient_ID = Q.Patient_ID

If you don't have a Patient_ID, then you really need to get one. At any
rate, replace the reference to Patient_ID with both Patient_Last_Name, and
Patient_First_Name in Query1 and then modify the Join in Query 2 to use
both
the first and last names.

HTH
Dale

Hi:
I'm using the following code to return certain records having a count > then
a certain number. I want to be able to enter and define this "having count
(*)" on a form. Can anyone help me with this?
Thanks in advance,
Steve

SELECT Link, Patient_Last_Name, Patient_First_Name,
Patient_Street_Address, Birth_Date, Date_Rx_Written
FROM dbo.tblMainDatabase
WHERE (Patient_Last_Name IN
(SELECT [patient_last_name]
FROM [tblMainDatabase] AS Tmp
GROUP BY [patient_last_name],
[patient_first_name]
HAVING COUNT(*) >2)) AND
(Date_Rx_Written
BETWEEN @enter_beginning_date AND @enter_ending_date)
ORDER BY Patient_Last_Name, Patient_First_Name

 

[John Vinson]

the contents of this field are
inconsistent between pharmacies in that some may use DL#s, some make up
thier own number, some leave the field blank, etc....I'll be uploading about
1.5million records into the database per month, so assigning a patient id on
my end is impossible. My plans are to run a threshold query based on the
query below to determine duplicate patients w/ multiple prescriptions.

So...

You have no way of uniquely identifying patients.
You must find all occurances of duplicate patient records.

These appear to be simply and purely contradictory requirements! I'm
not understanding what it is that you are counting.

John W. Vinson[MVP]