Distinct Count in Access 2002 report

Helllloooo...
For the life of me I cannot get a distinct count in an Access report.
I have numerous order ID entries and just want each to be counted only once.
Example:
1331
1331
1331
1331
----
1

1332
1332
------
1

1333
1333
1333
-----
1

And then tally up the number of unique orders (in this case , 3) at the bottom of report.

The report itself is built from a query.
I've tried every which way and cannot get it. It seems so easy!
Thanks-




wgraphicsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
You cannot do a COUNT(DISTINCT) in Access, but you can subquery it

select count(OrderID) from
(
select distinct OrderID from tbl
) AS fakealias

Open in new window

wgraphicsAuthor Commented:
Cyberkiwi,
That is sort-of-what-I thought from digging around.
I am out the door- but will try the subquery later at home or tomorrow. Not exactly sure how to do that? Do I place it in the query that the report is built off of (or one of the queries that make that query?)
Thanks!
Patrick MatthewsCommented:
wgraphics,

ck is correct that there is no native count distinct like there is in SQL Server.  The subquery approach is one way to do it; another would be to use a VBA function.

I elaborate on both methods in my article here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html

Patrick
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Patrick MatthewsCommented:
<offtopic>
Nice to have you back, ck!
</offtopic>
Jeffrey CoachmanMIS LiasonCommented:
If the report is grouped by the OrderID's, ... then you can simply get a count of the groups.

See here:
http://www.mvps.org/access/reports/rpt0016.htm

Sample attached as well

JeffCoachman
Access-EEQ-24643921-CountGroupsR.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wgraphicsAuthor Commented:
Well, after a bit of adjustments on the report, I was able to get to the number I needed by using the group by OrderID suggestion offered by boag. Thank you so much for your time and patience!
Jeffrey CoachmanMIS LiasonCommented:
great

Jeff
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.