running count of unique values in a ms access report

Posted on 2013-11-05
Medium Priority
Last Modified: 2013-11-17
I have a table that has a list of numbers that i would like to display a running count of unique value in a ms access report, this report has no grouping levels which makes it a bit challenging.Thanks
Question by:Svgmassive
LVL 10

Expert Comment

ID: 39625129
For a list of unique values:

SELECT DISTINCT fieldName FROM tableName;

For a count of unique values:

SELECT Count(*) AS N
(SELECT DISTINCT fieldName FROM tableName) AS T;
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39625133
is this what you are looking for

SELECT dbo_itemx.Sequencenumber, Count(dbo_itemx.Sequencenumber) AS CountOfSequencenumber
FROM dbo_itemx
GROUP BY dbo_itemx.Sequencenumber;

or maybe this is what you need

SELECT Q.Sequencenumber,
(select count(*) from
(SELECT dbo_itemx.Sequencenumber
FROM dbo_itemx
GROUP BY dbo_itemx.Sequencenumber
)  as Q1 where Q1.SequenceNumber<=Q.SequenceNumber) AS Expr1
(SELECT dbo_itemx.Sequencenumber
FROM dbo_itemx
GROUP BY dbo_itemx.Sequencenumber
) as Q;

it will help if you post a desired outcome/output
LVL 50

Accepted Solution

Dale Fye earned 2000 total points
ID: 39625235
You could use a function, see fnUniqueRowCount() in the attached database.

Add a control to your report, set the ControlSource to:


Then, change the Hide Duplicates (towards the bottom of the Format tab on the Properties dialog form) property to Yes.

Then, to make sure this works in normal view, report view, and print preview, you will need to call the function from the Report Open and Report Footers Format events, with the following code:


This will reset the counter when the report is opened or when the report footer is formatted.  The reason for the latter is that when you view the report in print preview, it runs through the report twice and if you don't reset the counter at the end of the report, the numbers will start a the last number + 1

Author Comment

ID: 39628243
fyed that's what i am looking,but a strange this is happening if i hide the control and hide duplicates the count is not correct.i just need it to display a total in the page footer per page

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Though there are a few manual ways to import PST files to Office 365 , third-party PST to Office 365 import tools are preferred over them due to various reasons.  Consequently, many tools or services are available for the same. Here, we pick the to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question