Grouping/counting

  • Thread starter Thread starter SF
  • Start date Start date
S

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
 
hi,
Unit # of Provinces # of Partners
------ --------------- -------------
Admin 2 Provinces 3 Partners
Program 2 Provinces 2 Partners

Could someone help me?
Click to expand...
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 <--
 
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
..

Stefan Hoffmann said:
hi,
Unit # of Provinces # of Partners
------ --------------- -------------
Admin 2 Provinces 3 Partners
Program 2 Provinces 2 Partners

Could someone help me?
Click to expand...
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 <--
Click to expand...
 
Back
Top