How do I translate my SQL server code to produce a distinct counts query in Access 2010?

Question

Related to the following:

What SQL can I use to retrieve counts from my payments data?

SQL : how can i count distinct record in MS ACCESS

Sample data:

Student ID |  School  | School Service Type | PaymentStartDate | PaymentEndDate | FiscalYear
   001          ABC              ED             2014-01-02         2014-08-02       2014
   001          ABC              ED             2014-06-02         2014-06-05       2014
   002          ABC              ED             2014-04-02         2014-08-05       2014     
   002          DEF              ED             2014-06-01         2014-06-05       2014
   002          DEF              ED             2014-02-01         2014-06-15       2014
   002          ABC              EZ             2014-03-01         2014-07-01       2014
   003          ABC              EZ             2014-01-30         2014-07-15       2014
   003          ABC              EZ             2014-03-01         2014-07-01       2014

What I want my count to look like:

  Month  |  FiscalYear  | School | School Service Type | Count

    May         2014        ABC            ED              2      
    May         2014        ABC            EZ              2
    May         2014        DEF            ED              1
   June         2014        ABC            ED              2      
   June         2014        ABC            EZ              2
   June         2014        DEF            ED              1
   June         2014        DEF            EZ              0

What my current SQL Server code looks like:

SELECT FiscalYear                            
      ,'11 May'              AS PAYMENT_MONTH                                 
      ,School                              
      ,School Service Type                              
      ,COUNT(DISTINCT Student ID) AS UNIQUE_STUDENT_COUNT   
  FROM [dbo].[MYODBCTABLE]                             
 WHERE FiscalYear = '2014'                   
   AND School Service Type IN ('ED', 'EZ')                                         
   AND MONTH(PaymentStartDate) = 05            
 GROUP BY                                        
       FiscalYear                                                          
      ,School                              
      ,School Service Type       
 ORDER BY 1,2,3
UNION
 SELECT FiscalYear                            
      ,'12 JUNE'              AS PAYMENT_MONTH                                 
      ,School                              
      ,School Service Type                              
      ,COUNT(DISTINCT Student ID) AS UNIQUE_STUDENT_COUNT   
  FROM [dbo].[MYODBCTABLE]                             
 WHERE FiscalYear = '2014'                   
   AND School Service Type IN ('ED', 'EZ')                                         
   AND MONTH(PaymentStartDate) = 06            
 GROUP BY                                        
       FiscalYear                                                          
      ,School                              
      ,School Service Type       
 ORDER BY 1,2,3

Desire: To create analogous SQL code that will allow me to perform this process in the Access 2010 environment.

Challenge:

1) Microsoft Access lacks the Count Distinct function.

I have been trying to follow the instructions laid out here, but I've had no luck in getting the SQL statements I derive to work properly.

As always, thank you for your time.

In an earlier question [here](http://stackoverflow.com/q/22102996/2144390) you were looking for a way to convert Access SQL to T-SQL so you could push the computations up to the SQL Server. Now you want to translate SQL Server code to Access SQL. If you already have working T-SQL code (that uses `COUNT(DISTINCT ...)`) then why not just use that by creating a Linked Table in Access that points to the View on the SQL Server? — Gord Thompson, Mar 06 '14 at 15:23
@GordThompson: If my SQL Server does not support Access, would the T-SQL code I use in Access to communicate with the SQL Server work? — ealfons1, Mar 06 '14 at 15:27
Please rephrase your question. I don't understand what you are asking. — Gord Thompson, Mar 06 '14 at 15:30
@GordThompson: My apologies. Is there such a thing as a SQL Server database that does not allow itself to be communicated to via the Microsoft Access 2010 program? Or, is there a situation where a person writes an Access query in T-SQL, and the SQL Server does not allow its execution? — ealfons1, Mar 06 '14 at 15:39

score 3 · Accepted Answer · answered Mar 06 '14 at 20:43

For what it's worth, this also works as a pass-through query in Access:

SELECT
     MONTH(PaymentStartDate) AS PAYMENT_MONTH
    ,FiscalYear
    ,School
    ,[School Service Type]
    ,COUNT(DISTINCT [Student ID]) AS UNIQUE_STUDENT_COUNT
FROM [dbo].[MYODBCTABLE]
WHERE FiscalYear = '2014'
    AND [School Service Type] IN ('ED', 'EZ')
GROUP BY
     MONTH(PaymentStartDate)
    ,FiscalYear
    ,School
    ,[School Service Type]
ORDER BY 1,2,3

That is, this:

produces this

Gord Thompson:Thank you very much for walking me through the steps. — ealfons1, Mar 06 '14 at 21:24

Andrew · Answer 2 · 2014-03-06T15:17:24.083

2

You have to get the distinct values first, and then count them. So you'll need some sort of sub-query to get the distinct set of student ids. I think you can get what you want this way: (psuedo-code)

This will be your inner query, getting your set of distinct students.

select
,'11 May'              AS PAYMENT_MONTH                                 
,School                              
,School Service Type
,Student id
from
dbo_ODBCTable
...
group by
School,
School Service Type,
Student id

Then you'll use that as a subquery to get your result:

select
payment_month,
school,
school service type,
count (student id)
from
( select
    ,'11 May'              AS PAYMENT_MONTH                                 
    ,School                              
    ,School Service Type
    ,Student id
    from
dbo_ODBCTable
...
    group by
    School,
    School Service Type,
    Student id)  t1
group by
payment_month,
school,
school service type

edited Mar 06 '14 at 15:17

answered Mar 06 '14 at 15:08

Andrew

8,445
3
28
46

In the "inner query" portion, is that where I grab the fields from my ODBC linked table? Do I reference my ODBC table in the "from..." as "from dbo_ODBCTable" portion of the inner query? – ealfons1 Mar 06 '14 at 15:14
:For some reason, Access does not allow me to execute do to the following error: "The SQL statement includes a reserved work, or an argument name that is misspelled or missing, or the punctuation is incorrect." – ealfons1 Mar 06 '14 at 15:32
Sorry, I'm pretty rusty on Access SQL. Make sure you put square brackets around the column names with space: `[School Service Type]`. Btw, I have to agree with Gordon, if it works in TSQL, why the heck would you want to do it in Access? – Andrew Mar 06 '14 at 15:38
I have been attempting to do this in T-SQL through Access (I am obligated to use Access for this project). Unfortunately, I was unable to create the tables necessary (due to SQL Server permission constraints) that would have allowed me to do so. – ealfons1 Mar 06 '14 at 15:40

How do I translate my SQL server code to produce a distinct counts query in Access 2010?

2 Answers2