How to execute the next tiered point with TSQL in Access 2010?

Question

I looked at the following relevant posts:

How to create a pivot table in Transact / SQL?

SQL Server Query - Choosing COUNT (*) with DISTINCT

SQL query to get the distribution of field values

Desire: data changes from state No. 1 to state No. 2.

Data: this data is the aggregate of the year (s) in which the person (identified by their personal identifier) ​​was recorded performing a specific activity in a specific place.

My data currently looks like this:

State #1

Row | Year | PlaceID | ActivityID | PersonID

001   2011    Park       Read         201a                
002   2011   Library     Read         202b  
003   2012   Library     Read         202b
004   2013   Library     Read         202b
005   2013   Museum      Read         202b
006   2011    Park       Read         203c
006   2010   Library     Read         203c
007   2012   Library     Read         204d 
008   2014   Library     Read         204d

Edit (4/2/2014): I decided that I want State # 2 to just be different.

I want my data to look like this:

State #2               

Row |  PlaceID |  Column1 | Column2 | Column3 

001      Park        2           
002     Library      1         1         1
003     Museum       1

Where:

Column 1: the number of people who visited PlaceID to read only one year.

2: , PlaceID, .

3: , PlaceID, .

№2 (). 2010, 2011, 2012 , 001, 3. , , .

(, , ):

, , , (, , ).

, T-SQL:

SELECT 
PlaceID
,PersonID
,[ActivityID]
,COUNT(DISTINCT [Year]) AS UNIQUE_YEAR_COUNT
FROM (
SELECT
     Year
    ,PlaceID
    ,ActivityID
    ,PersonID
FROM [my].[linkeddatabasetable]
WHERE ActivityID = 'Read') t1
GROUP BY
    PlaceID
    ,PersonID
    ,[ActivityID]
ORDER BY 1,2

, , .

Answer 1

, .

select placeID, 
  , Column1 = [1]
  , Column2 = [2]
  , Column3 = [3]
from 
(
SELECT 
    PlaceID
    ,COUNT(DISTINCT [Yearvalue]) AS UNIQUE_YEAR_COUNT
    FROM (
    SELECT
         yearValue
        ,PlaceID
        ,ActivityID
        ,PersonID
    FROM #SO
    WHERE ActivityID = 'Read') t1
    GROUP BY
        PlaceID
        ,PersonID
        ,[ActivityID]) up
pivot (count(UNIQUE_YEAR_COUNT) for UNIQUE_YEAR_COUNT in ([1],[2],[3]) ) as pvt

/ .

select I.PlaceID
    , Column1 = count(case when UNIQUE_YEAR_COUNT = 1 then PersonID else null end)
    , Column2 = count(case when UNIQUE_YEAR_COUNT = 2 then PersonID else null end)
    , Column3 = count(case when UNIQUE_YEAR_COUNT = 3 then PersonID else null end)
from (
    SELECT 
        PlaceID
        , PersonID
        ,COUNT(DISTINCT [Yearvalue]) AS UNIQUE_YEAR_COUNT
    FROM (
    SELECT
         yearValue
        ,PlaceID
        ,ActivityID
        ,PersonID
    FROM #SO
    WHERE ActivityID = 'Read') t1
    GROUP BY
        PlaceID
        ,PersonID
        ,[ActivityID]) I
group by I.PlaceID

Answer 2

Access, , .

DCOUNT(), http://office.microsoft.com/en-us/access-help/dcount-function-HA001228817.aspx.

() dcount ( "", " ", "placeid =" [placeid])