Counting folders

[JonathanT]

Thought this would be simple but I can't seem to find an answer. I want to count the number of unique values in a field and show that number on a report. The report is based on a select query that is based on a table "Videos". The records in the table have 3 fields. GBSize, VideoFilename, VideoFolderName, so there are duplicate VideoFolderNames in the records. There are approximately 5,000 files distributed between 116 folders in the table.



I have grouping on the report that allows me to list each VideoFolderName and the number of files in that folder. I can list the folders and the number of files in each folder on the report with no issues, however I want to list the number of Folders in the report header and can't seem to figure that out. Any help would be appreciated.

[June7]

Requires 2 queries. First query returns DISTINCT values for VideoFolderName. Second query counts records in first query.

Options for report:

1. build query object that has the DISTINCT VideoFolderName values. Then in textbox on report, DCount() expression:
=DCount("*", "qryFolders")

2. JOIN second query of folder counts to original table and use that as report RecordSource.

3. base report on GROUP BY query that has file totals by folder. Then report can calc Count(*) of folders with textbox expression. Unlike report based on raw table data, this will not allow display of detail records.


Any filtering applied to report would likely have to be repeated in the DCount() or query object.

[Ken Sheridan]

To count distinct values in a table you can use a subquery which returns DISTINCT values, and then count the numbers of rows returned by the subquery:

SELECT COUNT(*) AS FolderCount
FROM (SELECT DISTINCT VideoFolderName
      FROM Videos);

[JonathanT]

I created a query that just showed only the uniqe folder names using the code below:
SELECT DISTINCT
videos.VideoFolderName
FROM
videos;
Then I used a text box on the report with the following expression to count the folders: =DCount("*","FolderNames")

Thanks to all who helped.