?
Solved

count query question - access

Posted on 2013-01-21
5
Medium Priority
?
382 Views
Last Modified: 2013-01-21
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
Comment
Question by:joylene6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38803469
you can use the wizard to create the query, select the find duplicates query


test the query Find Duplicates
database--2-.mdb
0
 
LVL 1

Author Comment

by:joylene6
ID: 38803513
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
 
LVL 1

Author Comment

by:joylene6
ID: 38803522
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
 
LVL 1

Author Comment

by:joylene6
ID: 38803523
the HAVING Count(*)>2 worked btw
0
 
LVL 1

Author Closing Comment

by:joylene6
ID: 38803861
Thank you. using your example I was able to complete my query.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question