Report showing totals from two tables

[ThomasK via AccessMonster.com]

I am trying to build a report to give a total count of different types of
building inspections. I have two tables. One is called Inspections. It has
among it's fields, InspectionID, Inspector, InspectionType,and InspectionDate.
The other is called Reinspections. It's fields are ReinspectionID,
ReinspectionDate, and InspectionID (foreign key). They are linked one to many.
One inspection to one or more reinspections. There are several types of
inspections. I use a combo box to fill in the InspectionType field on my form.
There is only one type of reinspection but each inspection regaurdless of
type almost allways has more than one associated with it. The two tables are
linked by the InspectionID field.
I want a report to show the number (total count) of inspections by type and
also the number (total count) of reinspections. It needs to show this by
inspector and by a range of dates. (Between_ And_). I don't want to include
information about the inspections (InspectionDate) on the report, just the
Total counts for that time period.
I'm having a hard time building the report without grouping the inspections
by InspectionID. I just want all the totals on the page and nothing else.
Do I need to run two separate queries for the report?
Can I get the reinspection totals without grouping the inspections by
InspectionID?
How do I get the InspectionType totals on the same page?

Thanks for the help.

 

[Guest]

Thomas,

Unfortunately there is no simple sql that could give you the results you are
after. However this can be done with a bit more complex select:

select
i.InspectionType,
i.Inspection_Count,
Sum(r.ReInspectionCount) As ReInspection_Count
from
(select
InspectionType,
Count(InspectionId) As InspectionCount
from
InspectionsTable
where Inspector = [Enter Inspector]
and InspectionDate Between [Date From] And [Date To]
group by
InspectionType
) As i
left join
(select
ii.InspectionType,
Count(ri.InspectionId) As ReInspectionCount
from
InspectionsTable As ii
inner join
ReinspectionsTable As ri
on ii.InspectionID = ri.InspectionId
where i.Inspector = [Enter Inspector]
and i.InspectionDate Between [Date From] And [Date To]
group by
ii.InspectionType
) As r
on i.InspectionType = r.InspectionType

I assumed that you are interested in Date Range on original inspections, but
you should be able to easily modify the above to reflect your database
structure and the exact requirements. please note that the same (or similar)
conditions have to be specified twice - once for inspections count, and
another time - for reinspections count.

If you find that the query takes quite some time to run, consider creating
indexes on InspectionType, Inspector & InspectionDate fields.

HTH