A summing query?

[russweb]

I know I should be able to do this but...


I have a large column with the number of times different drugs have been prescribed. Each time a drug has been prescribed it is in the list.
I want to run a simple query which tells me the frequency of the values - i.e. that paracetamol was prescribed 184 times, codeine 73 times etc.

Please tell me in idiot speak how to do this.

Manythanks

[June7]

1. build GROUP BY (totals) query - query in design view, click Totals button (looks like Sigma) - group on the drug name and use Count function

or

2. build a report and use Grouping and Sorting with aggregate calcs - report will allow display of detail records and summary calcs

These are basic Access functionality and Access Help has guidelines, also lots of web tutorials.

[russweb]

1. build GROUP BY (totals) query - query in design view, click Totals button (looks like Sigma) - group on the drug name and use Count function

or

2. build a report and use Grouping and Sorting with aggregate calcs - report will allow display of detail records and summary calcs

These are basic Access functionality and Access Help has guidelines, also lots of web tutorials.

Jane
That's really helpful. Can you point me to good tutorial. I am doing report as you recommended.
I've added a group to the drug name which gives me name plus number of duplications for each time a specific patient is prescribed the same drug.
Now I want it to count the number of different patients which are prescribed each drug.
I have added drug name field as group.
I then added patient field as sort - what function/calculation do I need to do?

Thank you very much for your help.

I am used to SPSS where I would just ask for frequency

Hope you can help!

[recyan]

Till June's back, just check out if below gives some guidelines :

SELECT 
	qryDistinctDrugsPatient.Drug, 
	Count(qryDistinctDrugsPatient.PatientID) AS CountOfPatientID
FROM 
(
	SELECT DISTINCT 
		tblPatientsDrugs.Drug, 
		tblPatientsDrugs.PatientID
	FROM 
		tblPatientsDrugs
)
AS 
qryDistinctDrugsPatient
GROUP BY 
	qryDistinctDrugsPatient.Drug;

Thanks

[russweb]

Thanks very much.
Sorry, but I have no idea where to start with this. I am at such a low level, that I am only used to basic create table, query, report wizards.
Don't know where I should start entering this code.
Apologies for being a dumbo!

(I should have said I'm using Access 2010)

[recyan]

Method 1 :
Open a new Query Design Window & go in to its SQL view. Copy Paste the Query which I have posted, after replacing tblPatientsDrugs with your table name & Drug & PatientID with your corresponding field names.

Method 2 :
In your Query Builder View, first build the sub-query qryDistinctDrugsPatient & save it. Then use the sub-query to build the final query.

Let us know, if you face any problem in understanding above.

Thanks

[russweb]

Thanks again.

I am trying Method 2 and am interrogating the query.

As you know, I just want to count how many times the different drugs are prescribed.



And this is it in the report view:



I can't get my head around why it needs such a complex approach just to count the different values in a field...

May have to give up. but thanks for all your help and time

[recyan]

As you know, I just want to count how many times the different drugs are prescribed.

If I have understood correctly, you "want to count how many times the different drugs are prescribed.",
but want to count a Patient as 1, if the same drug has been prescribed to him / her multiple times.
For eg. :
Drug "A" has been prescribed to Patient "Tom" twice & to "Ben" once. You wan't the Count of Patients for Drug "A", to be considered as 2 and not 3.

Was not able to understand the Query Design View you have attached.

May have to give up.

???

Are you facing difficulty in using the Query Design View.
If yes, Just post your table structure (the table name & the field names), & am sure someone should be able to get you on the way.

Thanks

[June7]

Or you can see if the report can accomplish this with the raw data.

Group by drug section. Patients in detail section. Textbox in drug group section footer: =Count(PatientID).

A complication arises if each patient can have more than prescription for the same drug.

Again Access Help has guidelines. Tutorials on web. Bookstore.

http://office.microsoft.com/en-us/ac...001159160.aspx