Topics Search

count distinct on a report?

count distinct on a report?
Views: 34
I have a report that has two levels of information.

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:

More topics

 

Count Distinct Formula for Access Report

Preview
I have a table with various dates across all records (1/1/2012, 2/1/2012, etc) with some dates repeating multiple times. I have a report where I want to enter a formula in an unbound txtbox to count distinct how many different dates there are.

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

Preview
I'm looking for a workaround for the COUNT ( DISTINCT . ) AS N using Access 2007. I know I've got to select the distinct values in a nested sub-query or two, but I keep getting a syntax error with the following:
 

Extended DCount()

Preview
The built-in function - DCount() - cannot count the number of distinct values. The domain aggregate functions in Access are also quite inefficient.
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

Preview
I would like to count each distinct values from certain column in ACCESS DB. Let's say I have values like:
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?

Preview
I'm trying to count the number of "issues" produced in a report (the issue's are sorted by categories). The same issue can possibly appear twice in the report, if it applies to several different categories. I want the function to, however, only count the issue once.

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

Preview
This is my first post so hello all.
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

Preview
I have the table below. I am wanting to count distinct thread id from table1 for selected date.

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

Preview
I have a MySQL server running my .asp site. I have a table `log` where I record each instance of a user logging in. The main fields I'm concerned with are `time` and `user`.

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

Preview
I'm trying to return three columns of information with one column being distinct values and the other two columns of data of the distinct values only. I'm using access and was wondering if the column I'm trying to get Distinct is affected by being alphanumeric.

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

Preview
I'm trying to return three columns of information with one column being distinct values and the other two columns of data of the distinct values only. I'm using access and was wondering if the column I'm trying to get Distinct is affected by being alphanumeric.

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