Random SQL Queries to accomplish specific tasks.
When you need to count one record type that has multiple references to other record type. Normally with SQL Server you could use COUNT(DISTINCT s.StudendID) but MS Access does not allow the DISTINCT statement within a COUNT. Examples would be counting how may Customers have Orders or how may Students have Accounts;
SELECT COUNT(*) AS RecCnt FROM
(SELECT DISTINCT s.StudentID FROM Students AS s
INNER JOIN StudentAccounts AS a ON s.StudentID=a.StudentID
WHERE a.CompleteDate IS Null AND a.LDA IS Null);