SQL Select DISTINCT GROUP BY Weekday in Access

Question

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?

I think the `Date` you used as a column name have a conflict with the preserve keyword of access database.Try to change the `Date` column to another name? — amow, Mar 20 '15 at 07:33
Shouldn't be `Weekday(tableName.[Date])` instead of `Weekday(Date)`? Date is reserved word and must be arounded by `[]` brackets. — Maciej Los, Mar 20 '15 at 07:35
Hi amow, hi Maciej! I tried both, and it gives me still the same error.. — Alex, Mar 20 '15 at 07:38

score 1 · Accepted Answer · edited May 23 '17 at 12:10

1

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;

edited May 23 '17 at 12:10

Community

1
1

answered Mar 20 '15 at 07:42

Maciej Los

8,468
1
20
35

Maciej, this makes sense to me. I just seem to not be able to translate it to my needs: sSQL = "SELECT " & tablename & ".[UserID], COUNT(*) AS USER_COUNT" sSQL = sSQL & "FROM ( SELECT DISTINCT Weekday(" & tablename & ".[DATE]), " & tablename & ".[UserID]) FROM tablename) AS tmp" sSQL = sSQL & " GROUP BY UserID" ... this Syntax works, but it gives me the number of entries for each Weekday, and there is no distinct (i.e., some UserIDs should get counted for each date where they have data). – Alex Mar 20 '15 at 08:00
Awesome! This perfectly works. I really wonder how you guys can grasp some abstract code so fast.. I do understand somehow what I am doing, but my head hurts if I think too much about it. Thanks a lot Maciej! – Alex Mar 20 '15 at 08:20

SQL Select DISTINCT GROUP BY Weekday in Access

1 Answers1