General discussion

  • Creator
    Topic
  • #2112241

    Count(Distinct)in MS Access

    Locked

    by kwan_f ·

    What is the function in Access equivalent to Count(Distinct) in Oracle SQL? Can I build up an equivalent sql query in MS Access and export the query result to Excel?

All Comments

  • Author
    Replies
    • #3878210

      Count(Distinct)in MS Access

      by csn ·

      In reply to Count(Distinct)in MS Access

      I don’t know Oracle and can’t be sure the following MS Access syntax will give equivalent results:

      SELECT DISTINCT Count([tablename].[fieldname]) AS [name]
      FROM [tablename];

      Don’t enter brackets 🙂

      Once you have the query, export to Excelby:
      Right-click the query, select ‘Save-as/Export’, select ‘To an external file or database’ and the Excel as file-format.

      • #3832794

        Count(Distinct)in MS Access

        by kwan_f ·

        In reply to Count(Distinct)in MS Access

        The sql will not count the duplicate occurrence of an item as one.

    • #3878178

      Count(Distinct)in MS Access

      by bob sellman ·

      In reply to Count(Distinct)in MS Access

      I would suggest building a grouping query and then using the count function on the grouping query.

      The easiest way is to use the Access query builder to build a query with the one column (or combination of columns) that you want counted, but thenright click in the sort row and select Totals. A new row titled Total will appear in the query builder grid and it will say “group by”. If you run this query you will see one record for each distinct occurence of the column(s) you selected.

      If you also need to know how many or your original records match each distinct column/s then add an additional column to the query (you can use one of the columns already used if you want). Change the Total row for the additional column to Sum (select it from the drop down list). (You can also impose conditions by selecting Where from the drop down list and entering conditions in the criteria row.)

      If you add the sum column you could get results like:
      Vendor1 5
      Vendor2 8
      Vendor31

      The second column would be the number of entries (rows) for each vendor in this example.

      If you are looking for a result like the example above, then just select the export function from the File menu while you have the query highlighted in the Database Window and export as an Excel spreadsheet.

      If you prefer to export using VBA code, use the DoCmd.TransferSpreadsheet function, which is well documented in the Access help.

      • #3832795

        Count(Distinct)in MS Access

        by kwan_f ·

        In reply to Count(Distinct)in MS Access

        The sum function is only applicable to numeric field. It cannot substitute the count function.

    • #3831466

      Count(Distinct)in MS Access

      by m.r.chambers ·

      In reply to Count(Distinct)in MS Access

      Use the following:

      SELECT COUNT(DISTINCT yourfieldname) AS somefieldname
      FROM yourtable

      This should count only the distinct values, not the duplicates, then you can export it to excel using any of a number of means: from excel, you can importAccess information (using the database option in the Tools menu, or something like that: I can’t remember for sure), or you can use the Save As/Export command in Access. There are also some code based options in both programs, but these are the easiest and quickest if it’s just a once-off thing.

      Hope that helps,
      M.R.Chambers

      • #3831361

        Count(Distinct)in MS Access

        by kwan_f ·

        In reply to Count(Distinct)in MS Access

        I am using Access 97 and it does not accept Count(Distinct) command when building query.

    • #3830577

      Count(Distinct)in MS Access

      by dragon emperor ·

      In reply to Count(Distinct)in MS Access

      Unfortunately that convenient function does not exist outside of Oracle. The Count function in other databases is an aggregate function and does not work when trying to use with the Distinct statement and fields. The easiest way in Access to accomplist this is with 2 queries. In the first use the distinct statement to get a result set you want. Like: SELECT DISTINCT * FROM MyTable
      Then make a second query like this: SELECT COUNT(*) FROM MyQuery
      That should give the results you want.

    • #3831191

      Count(Distinct)in MS Access

      by kwan_f ·

      In reply to Count(Distinct)in MS Access

      This question was closed by the author

Viewing 4 reply threads