[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