[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2813
  • Last Modified:

running count of unique values in a ms access report

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
Database1.accdb
0
Svgmassive
Asked:
Svgmassive
1 Solution
 
etech0Commented:
For a list of unique values:

SELECT DISTINCT fieldName FROM tableName;

For a count of unique values:

SELECT Count(*) AS N
FROM
(SELECT DISTINCT fieldName FROM tableName) AS T;
0
 
Rey Obrero (Capricorn1)Commented:
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
FROM
(SELECT dbo_itemx.Sequencenumber
FROM dbo_itemx
GROUP BY dbo_itemx.Sequencenumber
) as Q;



it will help if you post a desired outcome/output
0
 
Dale FyeCommented:
You could use a function, see fnUniqueRowCount() in the attached database.

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

=fnUniqueRowCount([UniqueFieldName])

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:

=fnuniquerowcount(Null,True)

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
UniqueRowCount.accdb
0
 
SvgmassiveAuthor Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now