[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

count query question - access

I want to write a query that will count the number of clients on a certain class of drugs.
Then if they have more than 2 of those drugs on their profile, I want this on output. If they dont have 2 or more, I dont want them.

 I would love to do this in one query but I am thinking I have to make 2 queries.  One to count the drugs, then another one to ADD other information such as name, date, drug name .....

I have 2 tables I am gathering information from - ORDERS and Clients.  The Client number is a unique client identifier.

Criteria needed: End query must show clients who are on 2 or more drugs that start with the Thera_code of 08. The drug must not be stopped (NO in the Stopped column)

I have tried placing the "count" in the query, but I just cant get it to work!  I have attached the database for reference.


Here is the SQL without the COUNT:
SELECT CLIENTS.Client_NUM, ORDERS.THER_CODE_1, CLIENTS.NRS_STATION, CLIENTS.BED_ABBREV, CLIENTS.PTNAME, CLIENTS.PAT_NUM, ORDERS.DRUG_CODE, ORDERS.BRAND_NAME, ORDERS.GENERIC_NAME, ORDERS.STOPPED
FROM CLIENTS INNER JOIN ORDERS ON CLIENTS.Client_NUM = ORDERS.Client_NUM
WHERE (((ORDERS.THER_CODE_1) Like "08*") AND ((ORDERS.STOPPED)="NO"));
database--2-.mdb
0
joylene6
Asked:
joylene6
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use the wizard to create the query, select the find duplicates query


test the query Find Duplicates
database--2-.mdb
0
 
joylene6Author Commented:
I ranthis.

So you used the find duplicates wizzard..... I see in the Query in design view it has

In (SELECT [Client_NUM] FROM [DRUG Qry] As Tmp GROUP BY [Client_NUM],[THER_CODE_1] HAVING Count(*)>1  And [THER_CODE_1] = [DRUG Qry].[THER_CODE_1])

If I just want entries with more than 2 should I change to

In (SELECT [Client_NUM] FROM [DRUG Qry] As Tmp GROUP BY [Client_NUM],[THER_CODE_1] HAVING Count(*)>2  And [THER_CODE_1] = [DRUG Qry].[THER_CODE_1])
0
 
joylene6Author Commented:
Now if I want to modify this further to make only ONE of each drug code show..... do I do a MAX on the drug code? ( drug code is a unique identifier for each drug)

For example client is on 3 of drug A and 2 of drug B..... I just want one line of A and one line of B to show.
0
 
joylene6Author Commented:
the HAVING Count(*)>2 worked btw
0
 
joylene6Author Commented:
Thank you. using your example I was able to complete my query.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now