If anybody can help me figure this out, I would really appreciate it.

I have three tables:

Workers [key field = WorkerID]
Buildings [BuildingID]
WorkerAssessment [AssessmentID]

Each worker is assigned to one building. Each worker can have several assessments [a number from 1 to 4].

I want to figure counts and percentages of total for several different things [# workers assessed as 1, 2, 3, 4 or unassessed], grouped by building and by two other fields from the buildings table [office park and contractor]

I have two queries set up to establish and then return the most recent assessment for each worker:

The first query is [Set ARecent]:

SELECT Max([Worker Assessment].[Assessment Date]) AS MaxDate, [Worker Assessment].WorkerID

FROM [Worker Assessment]

GROUP BY [Worker Assessment].WorkerID;

The second query is [GetARecent]:

SELECT [SetARecent].WorkerID, [SetARecent].MaxDate, [Worker Assessment].Assessment

FROM [SetARecent]

INNER JOIN [Worker Assessment] ON [SetARecent].WorkerID = [Worker Assessment].WorkerID;

--------

The problem is when I try to figure the counts. I have a report set up grouped by building name, office park name [some buildings are part of an office park], and cleaning contractor name [each cleaning contractor cleans many buildings], and in the building name footer I am trying to find counts for workers assessed as 1, 2, 3, and 4 by using expressions like:

=Count(IIf([Qry1 for assessment grid - main]![Assessment]="1",1,0)) to figure the count of workers assessed as 1, etc.

[The Assessment value is being pulled from the GetARecent Query]

But this is returning the total number of workers in the building, office park, or contractor, rather than just the workers assessed as 1 in each group.

I have also tried it by creating 4 separate queries, 'CountA1', 'CountA2', 'CountA3', and 'CountA4'. [I am sure there is a better way to do this, but I don't know how]

These queries look like:

[CountA1]

SELECT Count(GetARecent.Assessment) AS CountOfAssessment
FROM GetARecent
HAVING (((GetARecent.Assessment)="1"));

Each query returns the correct number for the total workers in the query, but when I try to drop these values into the report [ie put the field [CountA1].[CountOfAssessment] in the report], it returns only the total number of workers assessed as 1 in the entire file, and I can't figure out how to break it down by building, office park, contractor, etc.

I assume that once I get the count part figured out, it will be pretty easy to figure the percentage of the total, although that may be a whole new stumbling block, since I haven't even tried it yet.


Thanks for any help you can give me.

-sarah

you wrote
<<
The problem is when I try to figure the counts. I have a report set up grouped by building name, office park name [some buildings are part of an office park], and cleaning contractor name [each cleaning contractor cleans many buildings], and in the building name footer I am trying to find counts for workers assessed as 1, 2, 3, and 4 by using expressions like:

=Count(IIf([Qry1 for assessment grid - main]![Assessment]="1",1,0)) to figure the count of workers assessed as 1, etc.
>>

Try "Sum" instead of "Count".

I'm not sure that will help but it might.

RichM