How do I set up an accurate count?

[Guest]

I have a database that listed multiple programs for households that we work
with. I need to "count" households with "headstart" as a program. This is
counted per caseworker. Example would be caseworker "AM" has 19 households
with headstart as a program.

Based on that query, I need to "count" the other programs that are listed
under these particular household. Example: Casworker "AM" has 19
households. Out of these 19 households, 12 of them had WIC.

I can query the headstart counter per caseworker, but I haven't been able to
query the second half. Does anyone have any suggestions?

 

[Guest]

I would approach this with a Divide And Conquer strategy. There may be other
more elegant ways.

First I'd create a query with the casework and households that have the
headstart program. Something like:

SELECT Cases.CaseworkerID, Cases.HouseholdID
FROM Cases
WHERE (((Cases.ProgramID)=1));

where I've assumed the Headstart program code is equal to 1.

Now you can create a query that totals the programs for each unique
caseworker/program combination limited by the restriction of the first query.
In query design view, show the first query and your Cases table. Establish
a link between the two tables on the Caseworker and Household fields.

Switch to Totals view (View, Totals). Drag the Caseworker field and the
Program field to the grid three times. Accept the default "Group By" in the
Caseworker and the first Program field. In the second Program field, set the
Total row to "Count". In the third, set it to "Where", and enter the
criteria of not equal to your Headstart code, so that it is not counted among
the "other" programs.

The resulting SQL will be something like:

SELECT Cases.CaseworkerID, Cases.ProgramID, Count(Cases.ProgramID) AS
CountOfProgramID
FROM Case1 INNER JOIN Cases ON (Case1.HouseholdID = Cases.HouseholdID) AND
(Case1.CaseworkerID = Cases.CaseworkerID)
WHERE (((Cases.ProgramID)<>1))
GROUP BY Cases.CaseworkerID, Cases.ProgramID;

Hope that helps.
Sprinks