count distinct on a report?

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:

Related Topics

Count Distinct Formula for Access Report
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
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()
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
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?
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
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
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
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
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
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


Exclude Unique Value Field From DISTINCT
Mates Need your Help!

I have report which is based On a QRY "QRYName" I use distinct, for example:

SELECT DISTINCT QUERYDATA.Product, QUERYDATA.Brand, QUERYDATA.Model,
FROM QUERYDATA

I want to add a bar code but how to exclude it from distinct?


How do I Count Only DISTINCT Values of a Field
I have an access 2000 database and was asked to do some reporting with the data in it.

There is one report I have to do would look like this :


count distinct records
with something that should be very basic but I cannot get it to work in MS Access 2007 despite lots of researching. Can someone please help me adapt the query below to count the number of distinct or unique "extensions"?

The table has 20+ columns but I only want to see 3 + a count column. I need the group by because the same extension could be listed multiple times with the same loc_ID, ROW_DATE and STARTTIME

SELECT root_hagent.loc_id, root_hagent.row_date, root_hagent.starttime, Count(root_hagent.extension) AS CountOfextension
FROM root_hagent
GROUP BY root_hagent.loc_id, root_hagent.row_date, root_hagent.starttime
HAVING (((root_hagent.row_date)>Now


Count Distinct in Access
I'm trying to do a select count distinct values for a table. Reading a few sites on google, I know that access does not support this. The database is for a library inventory system. I have 2 tables: Book_Copy and Inventory. Book_Copy has the columns (Copy_ID, Book_ID Checkout Date, Check In Date and Due Date, Status). The inventory table has columns: Inventory ID, Book _ID and Available Copies. I am trying to update the Inventory table, Available copies column based on the status (checked in) in the Book_Copy table. I have tried to write an update query trying different ways but all I end up getting is syntax errors. Any help would be appreciated. This is what I have tried:
UPDATE Inventory
SET Inventory.Available_Copies = (SELECT COUNT (*) AS Count from (Select DISTINCT BOOK_ID from BOOK_COPY where Book_Copy.Status_abbrv = 'CI');


Count Distinct in access while totaling up values
I have two columns of data something like this:

A | 11
A | 12
B | 14
C | 15
C | 42
C | 94
D | 12

I want my query to result in a distinct count of data in column 1 and sum of column 2. So result should be something like:
4 200

I think one way could be to first run a query to group by column 1 and sum of column 2 and then on resultant table run a count of column 1.

However I want to explore if there is a way within ms-access of skipping above make a single query.


Count Values in Totals for Reports
How does count values for totals in layout view work? I was hoping that it would count unique values in a column - but it does not appear to. I have an ID number column in my report - and the report is sorted by date. The same person/ID can have activity on several different dates. so they can have multiple rows in the report. I am trying to count each unique ID number in this report so I can provide a unique headcount for this report. I tried count values, and it gives me the same number as count records. Is there a way to count unique values in a column to show a total at the end of the report


Linking DISTINCT & NON DISTINCT Queries
s,
I have a question if it is possible and is the right way to join 2 Queries, 1 Distinct and the Second non Distinct
Both of them is working properly independently


Unique values across multiple columns
i have a data set I can't quite manipulate in excel (2003) the way I want to. When I pull the info into Access I can pull distinct records from my customer data (SELECT DISTINCT data.Account FROM data)and distinct records from my acct data (SELECT DISTINCT data.CUSTOMER_NAME FROM data).

What I'm having a hard time doing is querying the count of unique customers that occur on each unique account number.

I haven't used Access in a while and I'


Trouble creating a query with Joins
I have 2 tables (Documents and Sites) that I need to write a joined query for, and am having trouble. I have created one off queries to get information like Distinct count of location, but what I need to do is create a joined query that pulls together Site information (Count of distinct sites that have a document) and join it with location information sorted by Region.

Since each Site can have more than one document type I created a series of flags where if a document exists there is a 1, 0 for no document.

(I need to be careful not to count each document as aSite .)

Here are some of my existing queries:


Distinct count of records
I am trying to make a distinct count of records from a dataset that is generated by the following code:

sSql = "SELECT * FROM [Leverantie vs meetgegevens] WHERE [Leverantie vs meetgegevens].lev_id = " & CbLeverantie.Column(0) & " ORDER BY bmp_id, methode;"
[SF Leverantie overzicht].RowSource = sSql
[SF Leverantie overzicht].Requery

I would like to put the result in the textbox [Text12] on the form [Leverantie vs meetgegevens]. The dataset is shown in a listbox on the same form. The distinct count has to be performed on the variable [bmp_id].

Can anybody give me a suggestion how to do this? It would be nice to have something like:

Me![Text12].Value = DCount("SELECT DISTINCT [bmp_id]FROM [Leverantie vs meetgegevens]WHERE [Leverantie vs meetgegevens].lev_id = " & CbLeverantie.Column(0) & "")

(This code give the error: "Compile error. Argument not optional" on the DCount.)
Or is it possible to use the sSql (which contains the dataset) in a DCount