Access won't count correctly

[rachello89]

I have four tables in question:

tblEnrollments
tblEnrollment Product


tblProducts
tblServiceType

An enrollment can have many enrollmentproducts because students can change their particular plan for that enrollment. I am trying to create a query that counts the number of enrollments that have products with service type "C." The answer should be somewhere in the 50s; the answer that Access gives is 76. I believe what is happening is, Access is counting the enrollment products. For instance, if an enrollment has 3 products, it counts it as 3. It should be counted as 1. I am stumped about how to fix this. Thanks in advance for your help! Below is the SQL statement:

SELECT Count(tblEnrollments.EnrollmentID) AS CountOfEnrollmentID
FROM tblServiceType INNER JOIN (tblProducts INNER JOIN (tblEnrollments INNER JOIN tblEnrollmentProduct ON tblEnrollments.EnrollmentID = tblEnrollmentProduct.EnrollmentID) ON tblProducts.ProductID = tblEnrollmentProduct.EnrollmentProduct) ON tblServiceType.ServiceID = tblProducts.ServiceID
WHERE (((tblEnrollments.EnrollmentEndDate) Is Null) AND ((tblServiceType.ServiceID)="C")) OR (((tblEnrollments.EnrollmentEndDate)>[Start Date]) AND ((tblServiceType.ServiceID)="C"));

[jzwp11]

You have to group by the product.

I would alter the SELECT clause of your first query as follows and just have that query filter your dataset:


query name: Q1
SELECT tblEnrollmentProduct.EnrollmentProduct, tblEnrollments.EnrollmentID
FROM tblServiceType INNER JOIN (tblProducts INNER JOIN (tblEnrollments INNER JOIN tblEnrollmentProduct ON tblEnrollments.EnrollmentID = tblEnrollmentProduct.EnrollmentID) ON tblProducts.ProductID = tblEnrollmentProduct.EnrollmentProduct) ON tblServiceType.ServiceID = tblProducts.ServiceID
WHERE (((tblEnrollments.EnrollmentEndDate) Is Null) AND ((tblServiceType.ServiceID)="C")) OR (((tblEnrollments.EnrollmentEndDate)>[Start Date]) AND ((tblServiceType.ServiceID)="C"));

Then create a new query based on the above

SELECT Q1.EnrollmentProduct, Count(Q1.EnrollmentID) as CountOfEnrollmentID
FROM Q1
GROUP BY Q1.EnrollmentProduct