Svgmassive
asked on
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
Database1.accdb
is this what you are looking for
SELECT dbo_itemx.Sequencenumber, Count(dbo_itemx.Sequencenu mber) 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.Seque nceNumber) 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
SELECT dbo_itemx.Sequencenumber, Count(dbo_itemx.Sequencenu
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.Seque
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
SELECT DISTINCT fieldName FROM tableName;
For a count of unique values:
SELECT Count(*) AS N
FROM
(SELECT DISTINCT fieldName FROM tableName) AS T;