how to fix the query

The way the query is written you are counting Data.SC# field and the Data.Status grouped by Data.UserName, Data.YY, Data.MM, Data.Approach

If you want the Distinct SC# Count then that number will always be 1 provided that you add to the grouping the Data.SC# field.

So, assuming that I'm correct, I'd write the query as

SELECT Data.UserName, Data.YY, Data.MM, Count(Data.[Status]) AS [CountStatus], Data.Approach, 1 AS [Distinct SC# Count]
FROM qryResultsDumpInitial as Data
GROUP BY Data.UserName, Data.YY, Data.MM, Data.Approach, Data.[SC#];

Select allOpen in new window

Add DISTINCT to the SC# COUNT:

SELECT Data.UserName, Count(Distinct Data.[SC#]) AS [SC#Count], Data.YY, Data.MM, Count(Data.Status) AS CountOfStatus, Data.Approach
FROM qryResultsDumpInitial as Data
GROUP BY Data.UserName, Data.YY, Data.MM, Data.Approach;

Probably is unnecessary complex but it should work...give it a spin (fill the missing fields)

SELECT Table1.UserName, Table1.YY, Count(Table1.Status) AS CountOfStatus, CSCC
FROM qryResultsDumpInitial as Data INNER JOIN (SELECT Count(A.SCC) AS CSCC,A.UserName
FROM (SELECT DISTINCT Table1.UserName, Table1.SC  AS SCC
FROM qryResultsDumpInitial )  AS A
GROUP BY A.USERNAME
)  AS S ON Data.UserName = S.UserName
GROUP BY Data.UserName, Data.YY,S.CSCC;

Select allOpen in new window

You just need a subquery:

SELECT 
    Data.UserName, 
    Data.YY, 
    Data.MM, 
    Count(*) AS CountOfStatus, 
    Data.Approach,

    (SELECT 
        Count(*) AS [SC#Count]
    FROM 
        qryResultsDumpInitial as D
    WHERE
        Data.UserName = D.UserName AND
        Data.YY = D.YY AND
        Data.MM = D.MM AND
        Data.Approach = D.Approach
    GROUP BY
        [SC#]
    HAVING 
        Data.[SC#] = D.[SC#]) AS [DistinctSc#Count]

FROM 
    qryResultsDumpInitial as Data
GROUP BY 
    Data.UserName, 
    Data.YY, 
    Data.MM, 
    Data.Approach;

Select allOpen in new window

Roman,
The solution for T-SQL as suggested by Scott will not work if you are using Access SQL.  Keep in mind when posting SQL questions, that we need to know whether you are using Access SQL or the variant supported by the database you are linked to.

When using Count(xx), it is much more efficient to use Count(*) since that doesn't require the query engine to examine every row of the recordset to determine the count and sometimes it can actually use database statistics without having to pull any data at all.

Count(xxx) counts the instances of non-null values that were summarized away so if the query summarizes 1000 records and 1 of them is null for field xxx, the count will be 999.
Count(*) counts all the rows summarized by the query so using the above example, the count would be 1000.
Sometimes, you need to distinguish but most of the time, you are only trying to count the number of rows that were summarized away.

Once you understand how Count() works, you will understand why your version of the query returns 3 rather than 1.  Scott's method works  in T-SQL  but not Access and Gus' solution works regardless of the database engine.

And finally, it is poor practice to include special characters such as # or embedded spaces in your object names.  At a minimum, they force you to enclose all the offending names in square brackets.  To me, that just confuses the SQL String  since the [] can be confused with () and make the string look busier than it needs to.  There are other problems too as you will discover when building forms and writing code.

Experts exchange made life easer by allowing upload of needed objects to simplify work to other experts helping OPS.
A demo database will save us time and effort and help getting answers fast.

As clear as this question is, a demo wouldn't add anything.

Clear!
Of course, different experts!
What's wrong if one demands to understand while the issue is still unresolved?

Hamed,
Asking for a copy of the database for every question is not necessary and not productive.  In this case, do you want Roman to also send the SQL Server database?  If he doesn't, having the FE won't do you any good.

sorry, it took a while to return to you...Really sorry!!!
Gustav Brock: that is what I am getting as an error:

you tried to execute a query that does not include the specified expression
'Data.[SC#]=D.[SC#]' as part of an aggregate function

Try modifying the subquery:

    (SELECT 
        Count(*) AS [SC#Count]
    FROM 
        qryResultsDumpInitial as D
    WHERE
        Data.UserName = D.UserName AND
        Data.YY = D.YY AND
        Data.MM = D.MM AND
        Data.Approach = D.Approach AND
        Data.[SC#] = D.[SC#]
    GROUP BY
        [SC#]) AS [DistinctSc#Count]

Select allOpen in new window

still an error...

UserName      YY      MM      ChangedOn      CountOfStatus      SC#      Approach
Peter Pan      2019      3      3/1/2019      2      622232      Approach A
Peter Pan      2019      3      3/4/2019      1      871127      Approach A
Peter Pan      2019      2      2/26/2019      2      106642      Approach B
Peter Pan      2019      2      2/26/2019      3      108667      Approach B
Peter Pan      2019      2      2/26/2019      3      111364      Approach B
Peter Pan      2019      2      2/26/2019      3      111365      Approach B
Peter Pan      2019      2      2/26/2019      3      111366      Approach B
Peter Pan      2019      2      2/26/2019      3      111449      Approach B
Peter Pan      2019      2      2/26/2019      3      111591      Approach B
Peter Pan      2019      2      2/27/2019      1      117408      Approach B
Peter Pan      2019      3      3/4/2019      5      117408      Approach B
Peter Pan      2019      2      2/27/2019      3      117596      Approach B
Peter Pan      2019      2      2/26/2019      4      117598      Approach B
Peter Pan      2019      3      3/1/2019      3      119607      Approach B
Peter Pan      2019      2      2/26/2019      1      120211      Approach B
Peter Pan      2019      3      3/1/2019      4      120211      Approach B
Peter Pan      2019      3      3/4/2019      1      120211      Approach B
Peter Pan      2019      3      3/1/2019      4      122628      Approach B
Peter Pan      2019      3      3/4/2019      4      123419      Approach B
Peter Pan      2019      3      3/4/2019      4      123420      Approach B


how to implement the if statement the same like in excel
UserName      YY      MM      ChangedOn      CountOfStatus      SC#      Approach      CountSC#
Peter Pan      2019      3      3/1/2019      2      622232      Approach A             1
Peter Pan      2019      3      3/4/2019      1      871127      Approach A             1
Peter Pan      2019      2      2/26/2019      2      106642      Approach B           1
Peter Pan      2019      2      2/26/2019      3      108667      Approach B          1
Peter Pan      2019      2      2/26/2019      3      111364      Approach B            1
Peter Pan      2019      2      2/26/2019      3      111365      Approach B             1
Peter Pan      2019      2      2/26/2019      3      111366      Approach B             1
Peter Pan      2019      2      2/26/2019      3      111449      Approach B            1
Peter Pan      2019      2      2/26/2019      3      111591      Approach B             1
Peter Pan      2019      2      2/27/2019      1      117408      Approach B            0
Peter Pan      2019      3      3/4/2019      5      117408      Approach B           1
Peter Pan      2019      2      2/27/2019      3      117596      Approach B      1
Peter Pan      2019      2      2/26/2019      4      117598      Approach B      1
Peter Pan      2019      3      3/1/2019      3      119607      Approach B      1
Peter Pan      2019      2      2/26/2019      1      120211      Approach B      0
Peter Pan      2019      3      3/1/2019      4      120211      Approach B      0
Peter Pan      2019      3      3/4/2019      1      120211      Approach B      1
Peter Pan      2019      3      3/1/2019      4      122628      Approach B      1
Peter Pan      2019      3      3/4/2019      4      123419      Approach B      1
Peter Pan      2019      3      3/4/2019      4      123420      Approach B      1

if value in Approach =previous then 0, other wise 1
in this case it will work... the count is 17 (all distinct) versus all which is 20

If value in Approach =previous then 0, other wise 1

Then it should be:

Peter Pan      2019      2      2/26/2019     1      120211      Approach B      1
Peter Pan      2019      3      3/1/2019      4      120211      Approach B      0
Peter Pan      2019      3      3/4/2019      1      120211      Approach B      0

Select allOpen in new window

Or?

yes, exactly like this...

hi, guys! any updates?

I don't see a unique identifier in the sample data.  You will need a unique identifier AND need to sort on it in order to do what you are asking to do.  You will need a subquery that finds the max ID < the current ID because that is the record you need to compare to the current one.

Tables in DS view may look like spreadshets but they are quite different in how they operate.  Excel is a flat file and records have a permanently fixed position.  In a relational database, a query can return the selected rows in whatever order is convenient.  That is why you need a unique identifier.

i see, thank you