Grouping/counting

[SF]

I have a source table as follow:

Unit Provinces Partners
Admin P1 AA
Admin P1 BB
Admin P2 CC
Admin P2 AA
Program P1 DD
Program P3 EE

I want to construct a query to provide the following:

Unit # of Provinces # of Partners
------ --------------- -------------
Admin 2 Provinces 3 Partners
Program 2 Provinces 2 Partners

Could someone help me?

SF

 

[Stefan Hoffmann]

hi,

Unit # of Provinces # of Partners
------ --------------- -------------
Admin 2 Provinces 3 Partners
Program 2 Provinces 2 Partners

Could someone help me?

You need to queries to get the distinct count for each number you want.
Then create a third query to join them using [Unit].


mfG
--> stefan <--

 

[John Spencer]

PERHAPS the following will work
SELECT DISTINCT Unit
, SELECT(Count(*) FROM (SELECT DISTINCT Unit, Province FROM TheTable as X
WHERE X.Unit = Z.Unit))
, SELECT (Count(*) FROM (SELECT Distinct Unit, Province, Partner FROM
TheTable as Y WHERE Y.Unit =Z.Unit and Y.Province = Z.Province))
FROM TheTable as Z


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

hi,

Unit # of Provinces # of Partners
------ --------------- -------------
Admin 2 Provinces 3 Partners
Program 2 Provinces 2 Partners

Could someone help me?

You need to queries to get the distinct count for each number you want.
Then create a third query to join them using [Unit].


mfG
--> stefan <--