[AccessD] Two counts, one unique in pivot table

Or:

SELECT Count(*) AS Count_Visits, (SELECT COUNT(*) FROM (SELECT DISTINCT Patient_Number FROM tblTest)) AS Count_Distinct_Patients
FROM tblTest

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Mark A Matte
Sendt: 20. september 2011 21:17
Til: accessd at databaseadvisors.com
Emne: Re: [AccessD] Two counts, one unique in pivot table


Don't know about about a pivot...but:
 
select count(patient) as count_patient,sum(CountOfnumber) as visits
from
(SELECT tbl_test.patient, Count(tbl_test.number) AS CountOfnumber
FROM tbl_test
GROUP BY tbl_test.patient)

 
Hope it helps...
 
Mark A. Matte
 

> Date: Tue, 20 Sep 2011 16:45:42 +0100
> From: dc8 at btinternet.com
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Two counts, one unique in pivot table
> 
> Hi all,
> 
> I'm trying to produce a pivot table in Access that has two counts in it.
> 
> One of these is based upon a field that is unique within the data and 
> another that can have duplicates.
> 
> What I need to show is, based upon the small example, this
> 
> SAMPLE NUMBER PATIENT NUMBER
> A.1 1
> A.2 2
> A.3 2
> A.4 2
> A.5 3
> A.6 4
> A.7 4
> A.8 5
> A.9 5
> A.10 6
> 
> 10 6
> 
> which would show that there were 10 visits made by 6 distinct patients.
> 
> Could anyone point me in the right direction as to how to achieve this ?
> 
> Many thanks in advance,
> 
> Chris Swann
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
 		 	   		  
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com