DCount Distinct Values (1 Viewer)

AdamSmith

Registered User.
Local time
Today, 18:57
Joined
Jan 29, 2019
Messages
14
Hi everybody...

I'm trying to use a DCount expression. It's possible to get distinct values using a DCount in a form as Control Source, or using VBA?

I figure out a way to get distinct values by using SQL statement (in a query), like:
Code:
SELECT Count(*) AS [BookCount]
FROM (SELECT DISTINCT BookId FROM BorrowList);

but I can't write more than one statement in one query.

Anyone can help me?
 

Ranman256

Well-known member
Local time
Today, 11:57
Joined
Apr 9, 2015
Messages
4,339
Make Q1 that pulls the distinct values.
Or
Make Q2 that Counts the items in Q1.

Now Dcount Q1, or Dlookup Q2
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
To get a count of each BookID, use
Code:
SELECT BorrowList.BookID, Count(BorrowList.BookID) AS BookCount
FROM BorrowList
GROUP BY BorrowList.BookID;

or to filter for some value, then
Code:
SELECT BorrowList.BookID, Count(BorrowList.BookID) AS BookCount
FROM BorrowList
GROUP BY BorrowList.BookID
HAVING (((BorrowList.BookID)='SomeValue'));

As you are just using one table, you can shorten this by removing all references to 'BorrowList.' and unnecessary bracketing added by Access. For example

Code:
SELECT BookID, Count(BookID) AS BookCount
FROM BorrowList
GROUP BY BookID
HAVING BookID='SomeValue';
 
Last edited:

Users who are viewing this thread

Top Bottom