Your table structure is wrong. You should have a separate table for the
failures linked to your IncomingInspection table.
IF you cannot change the table structure, then you can use a UNION query to
normalize your data. Union queries cannot be built in the query grid, but
must be built using the SQL view. The union query would look something like
the following - substitute you field names and table name.
SELECT InspectionID, InspectionDate
, Failure1 as FailureType, Failure1Count As FailureCount
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure2, Failure2Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure3, Failure3Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure4, Failure4Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure5, Failure5Count
FROM YourTable
Now after you create and save that query, you can use it as if it were a
table and get your statistics from it. You could build a crosstab query
based on the saved union query.
TRANSFORM Sum(FailureCount) as TotalFailures
SELECT Format(InspectionDate,"YYYY-MM") as TheMonth,
Sum(FailureCount) as TotalFailuresForMonth
FROM SavedUnionQuery
WHERE InspectionDate Between #2007-01-01# and #2007-12-31#
GROUP BY Format(InspectionDate,"YYYY-MM") as TheMonth
PIVOT FailureType
You might consider posting your query (hint: View: SQL from the menu) if you
want specific advice on why it is failing. I suspect that you are only
applying the date criteria to one instance of the criteria.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..