General discussion

Locked

Count(Distinct)in MS Access

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?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Count(Distinct)in MS Access

by csn In reply to Count(Distinct)in MS Acce ...

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.

Collapse -

Count(Distinct)in MS Access

by kwan_f In reply to Count(Distinct)in MS Acce ...

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

Collapse -

Count(Distinct)in MS Access

by Bob Sellman In reply to Count(Distinct)in MS Acce ...

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.

Collapse -

Count(Distinct)in MS Access

by kwan_f In reply to Count(Distinct)in MS Acce ...

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

Collapse -

Count(Distinct)in MS Access

by M.R.Chambers In reply to Count(Distinct)in MS Acce ...

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

Collapse -

Count(Distinct)in MS Access

by kwan_f In reply to Count(Distinct)in MS Acce ...

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

Collapse -

Count(Distinct)in MS Access

by Dragon Emperor In reply to Count(Distinct)in MS Acce ...

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.

Collapse -

Count(Distinct)in MS Access

by kwan_f In reply to Count(Distinct)in MS Acce ...

Poster rated this answer

Collapse -

Count(Distinct)in MS Access

by kwan_f In reply to Count(Distinct)in MS Acce ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums