Post reply

Distinct Count

  • September 4, 2002 at 3:37 pm

    #158682

    I am having trouble finding a decent workaround for trying to get a distinct count out of a table in an access database.

    If I do a select distinct statement, and set the result in a recordset - I cannot get a count of records in the recordset - DAO does not seem to work in VBA - only ADO and ADO doesn't seem to have a recordcount property.

    I also can't do a select distinct count statement (tried various syntax) - always gives syntax error.

    Anyone have any suggestions? Thanks!!

    Edited by - rachel on 09/04/2002 3:38:29 PM

  • September 5, 2002 at 12:59 am

    #435950

    Î was amazed to find that a normal SELECT COUNT(DISTINCT columnname) FROM SOMETABLE didn't work... Thought Access 2000 supported most of the syntax in SQL 2000. Well, this query works at least:

    SELECT COUNT(*)

    FROM (SELECT DISTINCT Field1 FROM Table1) AS a;

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply