DCount Distinct Values

[AdamSmith]

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:

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]

Make Q1 that pulls the distinct values.
Or
Make Q2 that Counts the items in Q1.

Now Dcount Q1, or Dlookup Q2

 

[isladogs]

To get a count of each BookID, use

SELECT BorrowList.BookID, Count(BorrowList.BookID) AS BookCount
FROM BorrowList
GROUP BY BorrowList.BookID;

or to filter for some value, then

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

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