Counting checkboxes between two dates

[ThomasK via AccessMonster.com]

I'm tring to build a query that will count the number of checkboxes between
the dates of two other fields. I have two tables within the query. One table
(tblInspection) contains InspectionID, InspectionDate, and Citation, among
others. The other table (tblReinspection) contains ReinspectionID, and
ReinspectionDate, among others. There is a one to many relationship between
the two tables, tblInspection being the parent table. Only one InspectionDate
will be entered but none or many ReinspectionDates may be entered. The
Citation field is a checkbox.

What I am tring to do is to find all the records that have date entries
between two dates (inspection or reinspection dates) and then count all of
the records with checkmarks in the citation field. The problem is that
several records for the same inspection because there may be several
reinspection dates. When I try to count the checkmarks it counts one for each
record. I need it to count one for each inspection.

This is where I started.

SELECT tblInspections.InspectionDate, tblReinspections.ReinspectionDate,
tblInspections.Citation
FROM tblInspections INNER JOIN tblReinspections ON tblInspections.
InspectionID = tblReinspections.InspectionID;

Thanks for the help.

 

[giorgio rancati]

Hi ThomasK

if I understood, try it
----
SELECT tblInspections.InspectionDate, tblReinspections.ReinspectionDate,
tblInspections.Citation,tblInspections1.CountOfCitation
FROM
(( SELECT
InspectionDate,
Count(Citation) AS CountOfCitation
FROM tblInspections
WHERE tblInspections.Citation=True
GROUP BY InspectionDate
) AS tblInspections1
INNER JOIN tblInspections ON
tblInspections1.InspectionDate=tblInspections.InspectionDate)
INNER JOIN tblReinspections ON tblInspections.InspectionID =
tblReinspections.InspectionID
WHERE tblInspections.Citation=True
AND tblReinspections.ReinspectionDate Between #01/01/2007# AND
#01/31/2007#
----

you can also make a query with the derived table
qryInspections1
----
SELECT
InspectionDate,
Count(Citation) AS CountOfCitation
FROM tblInspections
WHERE tblInspections.Citation=True
GROUP BY InspectionDate
---

then use it in principal query
----
SELECT tblInspections.InspectionDate, tblReinspections.ReinspectionDate,
tblInspections.Citation,qryInspections1.CountOfCitation
FROM (qryInspections1
INNER JOIN tblInspections ON
qryInspections1.InspectionDate=tblInspections.InspectionDate)
INNER JOIN tblReinspections ON tblInspections.InspectionID =
tblReinspections.InspectionID
WHERE tblInspections.Citation=True
AND tblReinspections.ReinspectionDate Between #01/01/2007# AND
#01/31/2007#

 

[ThomasK via AccessMonster.com]

Thanks for your help.
The problem Im having with your responce is that I have only made queries in
design view. So I'm having a little trouble understanding. So, the solution
involves a query quering the results of another query?

Tom