Counting unique numbers

[karen]

I have a table containing loan account information for
loans that were charged a fee. Some loans were charged
this fee more than once. I need to know how many
DIFFERENT accounts there are in the whole table, not
total records. In other words, records (or account
numbers) that appear more than once should count only
once.



I thought of grouping and then counting the groups, but
the table has about 170,000 records in it. So I hope
there is an easier way!



Thanks for your help.



Karen

 

[Newbie]

How do you want to use this number. Is it just an adhoc query where you can
look at the number of records via the record selectors

If yes, assuming that the table only contains one type of record then the
following should work
SELECT DISTINCT yourtablename.AccountNo
FROM yourtablename

otherwise you could create a recordset and then perform a recordcount on the
recordset to get the number of records

HTH

 

[Duane Hookom]

If your table is properly indexed then grouping and counting groups is a
walk in the park. I am not aware of an easier or more efficient method.

 

[Treebeard]

SELECT Count(*) As Account_count FROM (SELECT DISTINCT [AccountNo] FROM
yourtablename);