I thought I had a grasp on this but I am coming up empty. I have a form that I want to show a count of specified Fields within a recordset (QAU). I have been able to come up with a SQL that will return the value I want, but going about it this method would require me to setup 6 seperate SQL statements. I have tried running the recordset in vba and called the result for the count I want with the .value = rst!CountofQAU approach, with no luck.
Is there a better way? I am trying to embed this in the form if possible, but I know you cannot set the Control Source to an SQL even if it only returns 1 result.
Here is my SQL: (Thank You Doug)
CODE
SELECT Count(*) AS CountOfQAU, Zone
FROM
(SELECT DISTINCT tblDispatchedQAU.QAU, tblState.Zone
FROM tblDispatchedQAU INNER JOIN tblState ON tblDispatchedQAU.State = tblState.State
WHERE tblState.Zon)='East (Miami)')
GROUP BY Zone;
FROM
(SELECT DISTINCT tblDispatchedQAU.QAU, tblState.Zone
FROM tblDispatchedQAU INNER JOIN tblState ON tblDispatchedQAU.State = tblState.State
WHERE tblState.Zon)='East (Miami)')
GROUP BY Zone;
My form looks like this:
East QAU Count: Count
West QAU Count: Count
Central QAU Count: Count
and so on.
