CODEWITHSUNDEEP
sqlms-accesscountdistinct
Amanda Urena
Amanda Urena

Reputation: 1

Microsoft Access -> Distinct Count with inner Join and multiple selects

I've attempted a few iterations of this query and keep getting syntax errors. I tried making it a subquery at one point but with the inner join it was coming up with additional syntax errors. Any advice would be super appreciated.

I'm try to pull the contacts per staff member.

SELECT Stafflist.Staff, Count(distinct Contact) as count
FROM Contacts INNER JOIN
     StaffList
     ON Contacts.ID = Stafflist.ID
WHERE ((Contacts.Date) Between #1/1/2020# And #1/5/2020#) AND Contacts.status='Finished')
GROUP BY Staff,
ORDER BY Staff;

Upvotes: 0

Views: 406

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

MS Access doesn't support COUNT(DISTINCT). But you can use a subquery:

SELECT Stafflist.Staff, Count(Contct) as count
FROM (SELECT DISTINCT Stafflist.Staff, Contacts.Contact
      FROM Contacts INNER JOIN
           StaffList
           ON Contacts.ID = Stafflist.ID
      WHERE Contacts.Date Between #1/1/2020# And #1/5/2020# AND
            Contacts.status = 'Finished'
     ) as SC
GROUP BY Staff,
ORDER BY Staff;

Upvotes: 2

Related Questions