CODEWITHSUNDEEP
sqldatabasems-accesssyntaxvba
Alex
Alex

Reputation: 3431

SQL Select DISTINCT GROUP BY Weekday in Access

I am trying to Count the distinct Number of UserID's in a table for each Weekday (e.g. 545 UserID's on Weekday 1 = Monday, 120 UserID's on Weekday 2 = Tuesday etc.). I am doing this in Access Visual Basic, but the syntax should be universal to SQL. Here is my VB Code:

 sSQL = " SELECT Weekday(" & tablename & ".[DATE]) AS WEEKDAY, Count(DISTINCT " & tablename & ".[UserID]) AS User_COUNT"
 sSQL = sSQL & " FROM " & tablename
 sSQL = sSQL & " GROUP BY Weekday(" & tablename & ".[DATE])"
 qdf.SQL = sSQL

The plain SQL Syntax should look like this (edited based on comments & test):

     SELECT Weekday(tbl.[Date]) AS WEEKDAY, Count(DISTINCT tbl.[UserID]) AS User_COUNT
     FROM tbl
     GROUP BY Weekday(tbl.[Date])

..whereas [Date] is a field in tbl formatted as Datetime and [UserID] is a field formatted as Long (with duplicates).

When I try to run the command it tells me "Missing Operator in Query-Syntax.." Is this a problem of my VB Code or is the SQL Syntax wrong?

Upvotes: 0

Views: 626

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

MS Access database engine does not support COUNT(DISTINCT ...).

To workaroud it, please see this thread: SQL : how can i count distinct record in MS ACCESS where author suggests to solve issue by using subquery:

SELECT
    user_id
  , COUNT(*) AS count_distinct_clients
FROM
    ( SELECT DISTINCT
          user_id, 
          client_id
      FROM tbl_sActivity
    ) AS tmp
GROUP BY
    user_id ;

Change the query code to your needs.

[EDIT]

SELECT
    wday, COUNT(UserId) AS count_distinct_users
FROM
    ( SELECT DISTINCT WEEKDAY([Date]) AS wday, UserId
      FROM tblName
    ) AS tmp
GROUP BY
    wday;

Upvotes: 1

Related Questions