Access SQL - Show Additional Columns in Count Query

Question

I'm trying to get detailed list of duplicates, the Foldername is the duplicate value. I need count of duplicates instances AND other corresponding columns such as FullPath for each instance of a duplicate.

I'm trying to return a list of every single fullpath with it's corresponding foldername count, as well as additional columns such as unit, size and so forth.

FolderName (has the possibility of a duplicate)
FullPath (is completly unique)

I've give a few stabs at this; in SQL Server this seems more apparent... but in Access I'm a bit lost here.

This is the basic SQL I've come up with so far:

    /* ''''''''''''''Works but doesn't return Count'''''''''''''' */
Select Snapshot.Unit, Snapshot.FolderName, Snapshot.FullPath
From Snapshot
Where Snapshot.FolderName in 
(
    Select Snapshot.FolderName
    From Snapshot
    Group by Snapshot.Foldername
    Having Count(Snapshot.FolderName)> 1
)
Order by Snapshot.FolderName, Snapshot.FullPath

Here's an example output that I'd like to get:

Unit       FolderName     FullPath                          Count
BCU        Misc           C:\blah\blah\blah\Misc            2
ENV        Misc           R:\blah\blah\blah\Misc            2
CLR        Monkey         Q:\blah\blah\blah\blah\Monkey     17
ATL        Zebra          Z:\blah\blah\zoo\Zebra            24

I referenced: Having trouble using count() in Access SQL query

Answer 1

Do the counting in your subquery and INNER JOIN Snapshot to the subquery.

SELECT s.Unit, s.FolderName, s.FullPath, sub.num_dupes
FROM
    Snapshot AS s
    INNER JOIN
    (
        SELECT FolderName, Count(*) AS num_dupes
        FROM Snapshot
        GROUP BY FolderName
        HAVING Count(*)> 1
    ) AS sub
    ON s.FolderName = sub.FolderName
ORDER BY s.FolderName, s.FullPath