# count distinct on a report?

Views: 35

Is there any way to count the first column distinctively?

Please see the example below:

abc

erd

Right now, it's counting 3 records of 00001 instead of 1. I want it to show 1 on the report to say there is ONE

Sponsored Links:

Thank you for taking the time to report an issue.

What's wrong... Please write below.

## More topics

### Count Distinct Formula for Access Report

I've looked on net and this forum but usually find answers that involve writing queries or some custom code. I really just want/need a formula (to put in recordsource of txtbox).

So this sample data set would give me a result of 4. There are 6 dates but only 4 unique or distinct dates.

### COUNT ( DISTINCT ... ) Issue

### Extended DCount()

ECount() offers an extra argument so you can count distinct values. The other arguments are the same as DCount().

Code to count the number of records in a table/query in Microsoft Access, with the option to count the number of distinct values. A replacement for DCount().

### Access DB distinct count

1

1

1

2

3

3

3

3

I need to get the number of each distinct values and show the value, something like:

1, 3

2, 1

3, 4

I tried using:

qry = "SELECT COUNT(*) FROM (SELECT DISTINCT description FROM expenses GROUP BY description)"

and some variations of this query, but e.g. this query just shows total number of distinct values. DB version is ACCESS 2k. Is there a way to do what I'm after?

### DCount / Count(if) within Report?

Right now this code:

=Count(IIf(([IssueRisk]="High") And [IssueSource]="Computer" And ([CurrentStatus]="In Progress"),0))

returns the correct count - but will duplicate count the same issue if it appears in two categories. How can I only count distinct values?

I tried coding this:

=DCount("[IssueID]","2012 Report","[pkPersonID]=Forms!frmReporting!ChoosePerson AND [IssueRisk] = " & Chr(34) & "HIGH" & Chr(34))

It works - however, I can't see how I can add the other critiria (i.e// issue soure, and current status

### Counting distinct id's

I am new to access and need some help around counting distinct id's.

I need to count the number of distinct customer id's and also count the number of orders made.

### COUNT DISTINCT

ID | LOGIN DATES | THREAD ID | MYMONTH

2 | 4/25/2012 | AA-123-R11 | 4

3 | 4/21/2012 | AA-123-R12 | 4

...

expected result

LOGIN DATES | Count of Unique Ids

25-Apr-12 | 4

### Unique user analysis over time

I'm trying to find the unique number of users each month, and also determine how many are "new", how many have "left",

My first query to count the unique number of users for October so far was simple:

SELECT COUNT(DISTINCT(USER)) FROM LOG WHERE TIME > "2009-10-01"

And to count the distinct number of users for September was also simple: SELECT COUNT(DISTINCT(USER)) FROM LOG WHERE TIME < "2009-10-01" AND TIME > "2009-09-01"

I thought I'd get clever with trying to find out how many people in October did not log in during September:

SELECT COUNT(DISTINCT(USER)) FROM LOG WHERE TIME > "2009-10-01" AND USER NOT IN (SELECT COUNT(DISTINCT(USER)) FROM LOG WHERE TIME < "2009-10-01" AND TIME > "2009-09-01")

But that must have been a very inefficient way to do things, because the query ran for over 3 minutes without returning any results. I finally canceled it.

Before I go any further, is there a "normal practice" way to go about this?

### Sql code for Distinct column and two other Columns

SELECT Order Num, Trade Date,Cost Center Code,Man-Semi-Auto

FROM 2011 Report

WHERE Order Num IN (SELECT DISTINCT Order Num FROM 2011 Report);

I though this would work but it is not returning Distinct or unique entries

### Sql code for Distinct column and two other Columns

SELECT Order Num, Trade Date,Cost Center Code,Man-Semi-Auto

FROM 2011 Report

WHERE Order Num IN (SELECT DISTINCT Order Num FROM 2011 Report);

I though this would work but it is not returning Distinct or unique entries