Distinct Count

• rachel

  SSCommitted

  Points: 1626

  More actions

  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

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  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/