Function to count unique records?

  • Thread starter Thread starter ThriftyFinanceGirl
  • Start date Start date
T

ThriftyFinanceGirl

I know there must be a way to do this, but I'm not finding a function to use
in my code.... anyone know one?
 
On Tue, 10 Nov 2009 19:26:58 -0800, ThriftyFinanceGirl

You could create a query with unique records using the DISTINCT
keyword (or set the Unique Values property of the query). Then create
a second query based on the first one, and use the Count function:
select count(*)
from myTable

-Tom.
Microsoft Access MVP
 
ThriftyFinanceGirl said:
I know there must be a way to do this, but I'm not finding a function to use
in my code.... anyone know one?
Click to expand...


The easiest way to extract unique records is to run a query
with its Unique Values property set to yes. In SQL view,the
query could be something like:

SELECT DISTINCT fielda, fieldb, ...
FROM table

Once you have created your query, you can use
DCount("*", "query")
in a VBA procedure or a text box expression to get the
number.

What do you want to do with the count?

Why do you have a table with duplicate records?
 
Actually I have three tables... Reports... Versions... Runs.... and I need to
know how many users there are for a version, but the users are attached to
the Runs table, so I have a single user for each run, but I don't need to
count the runs, I need to count the distinct users. Thanks!
 
Quoting Allen Browne:

Okay, this gets asked reasonably often, so here is a new article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
OK, I think I see where you are coming from. Did you
resolve your problem or was there a follow up question?
 
Oooooooh, Cool! Thanks!

John Spencer said:
Quoting Allen Browne:

Okay, this gets asked reasonably often, so here is a new article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


.
Click to expand...
 
No, I got it now. Thanks so much for your help! :-)

Marshall Barton said:
OK, I think I see where you are coming from. Did you
resolve your problem or was there a follow up question?
--
Marsh
MVP [MS Access]

Actually I have three tables... Reports... Versions... Runs.... and I need to
know how many users there are for a version, but the users are attached to
the Runs table, so I have a single user for each run, but I don't need to
count the runs, I need to count the distinct users. Thanks!
Click to expand...
.
Click to expand...
 
Back
Top