[AccessD] Mni SubQuery Tutorial WAS RE: calculation

I was a little short on time yesterday, but I was going to explain how
my solution to this worked from a subquery point of view.  Subqueries
are very handy tools, so here is a little more detail on how to use one
in this particular scenario:

First, we had a table design of ID(Long), Patient(Text), and B(Long).
Sample Data:

ID	Patient	B
1	AA		1
2	AA		1
3	AA		2
4	AA		3
5	BB		1
6	AA		4
7	BB		2

What we are looking for, is a breakdown of how Patient to B groups in
percentages.  (So we need a count of how many records per patient, and
how many records of a B record (per patient)).  To get either one, is
simply a Count in a Totals query:

SELECT Patient, B, Count(B) AS CountOfB 
FROM tblPatients
GROUP BY Patient, B;

Would return:

AA	1	2
AA	2	1
AA	3	1	
AA	4	1
BB	1	1
BB	2	1

But to know the percentage of each B value, with the patient subset, we
need a count of how many records there are per patient.  A subquery does
this just fine:

First, we will need to pass data from the main query to the subquery, to
do this, you must Alias the table in the main query.  (In design view,
right click the table and select properties, and then change the Alias
to T1).

Then, in a field, we can put in a new field (going step by step here):

(SELECT Count(*) FROM tblPatients WHERE Patient=T1.Patient)

Add this to the query above, and the resulting data set is (in a Totals
query, you will need to set this field as an Expression):

AA	1	2	5
AA	2	1	5
AA	3	1	5
AA	4	1	5
BB	1	1	2
BB	2	1	2

So what is happening, is that we are running another query, inside our
main query (ie, a subquery), and as part of the criteria, we are feeding
it T1.Patient, which is the current patient value in the current field
of the root query.  (So the subquery runs for each record returned by
the main query, using the values from each recored wherever the alias
(in this case T1) is used the subquery).

Whalla, now to get a percentage, we just have to combine the last two
fields with a little math:

SELECT Patient, B, Count(B)/ (SELECT Count(*) FROM tblPatients WHERE
Patient=T1.Patient)*100 AS Percentage
FROM tblPatients AS T1
GROUP BY Patient, B;

I believe my initial solution used 2 subqueries, instead of just
Count(B) for the first part.  But now our resulting data is:

AA	1	40
AA	2	20
AA	3	20	
AA	4	20
BB	1	50
BB	2	50

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
Sent: Monday, July 13, 2009 2:37 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] calculation

An extra criteria at B "3" wil give me the B3 percentages.

Best Wishes

Pedro

The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.