Subgroup or Subreport?

  • Thread starter Thread starter bhammerstrom.work
  • Start date Start date
B

bhammerstrom.work

OK, I'm in over my head and need some more help.

The setup: The database has Places (addresses) where there are many
Inspections. The Inspections are at one Place and one InspectorDate.
Inspections have many Observations. One Observation can have either
one Issue filled-in (Observed Defective), or left Null (Not Observed).

Now the catch: One Place can have many InspectionTypes, and One Issue
can have many Inspection Types. The tblInspectionTypes is between them
separated by two junction tables, IssueDetails and PlaceDetails.
That's it.

What the report needs to do is group by Issue, then show all the
Observations where the PlaceInspType matches the IssueInspType,
grouped by GroupObservation. This will group a list of Defective
Observations followed by a list of Places also inspected by that
Issue's InspectionType, but where that Issue was not observed. Still
with me?

I created this query, below, that finally does all this, but I can't
get the subgroup to work in the report. Anyway, the idea is that I
want the report to group first by Issue, then by GroupObservation, and
give me a count of Places observed defective for each issue, along
with a count of Places also observed but that Issue was not observed.

SELECT qryObsWhereIssueInspMatchesPlaceInsp.ObservationID,
IIf(IsNull([ObservationID]),"Not Observed","Observed Defective") AS
GroupObservation, qryIssueInspMatchesPlaceInsp.Issue,
qryIssueInspMatchesPlaceInsp.Address
FROM qryIssueInspMatchesPlaceInsp LEFT JOIN
qryObsWhereIssueInspMatchesPlaceInsp ON (qryIssueInspMatchesPlaceInsp.
[tblIssueDet-Issue_ID] =
qryObsWhereIssueInspMatchesPlaceInsp.Issue_ID) AND
(qryIssueInspMatchesPlaceInsp.[tblPlacesDetail-Place_ID] =
qryObsWhereIssueInspMatchesPlaceInsp.PlaceID)
GROUP BY qryObsWhereIssueInspMatchesPlaceInsp.ObservationID,
qryIssueInspMatchesPlaceInsp.Issue,
qryIssueInspMatchesPlaceInsp.Address;

Can I do this with nested Groups? or do I need to do it with a
subreport?
Ideas?

-Brad
 
Well, I got this to work on my test data!!--next I'll try on a big
data set. Here's how I did it:

The way I got it to work was not with a subreport, but with three
levels of Groups: first Grouping on Issue, then a SubGrouping on
GroupObservation, then SubGrouping on Address. In the footer for Issue
I have text boxes that display a summary of all the various counts.
The (not visible) counts are calculated in the Group footers:

Down in the third level Address footer I have a simple text box
counter, txtSumCountAddresses (Control Source=1, with Running Sum Over
Group) which totals the Addresses in each of the two of
GroupObservation groups.

Then to get total Addresses inspectected (for both groups) I put
another text box in the GroupObservation footer that refers to the
counter and does a running count called, txtTotalInspected (Control
Source=[txtSumCountAddresses], with Running Sum Over Group).

For a count of Observations of the Issue (not Addresses), another text
box in the GroupObservation footer is called, txtObsCount that only
counts records with an ObservationID (Control
Source=Count(IIf([ObservationID]<>0,"1",Null), with Running Sum No).

The last calculation counts the Addresses (not observations) also
inspected, but where that Issue was not observed. Call it txtUnitsOK
(Control Source=Count(IIf(IsNull([ObservationID]),"1",Null)), with
Running Sum No).

The fourth number to find is the count of Addressess (not
observations) where the Issue was observed, and is simply
=[txtTotalInspected]-[txtUnitsOK].

Works for me!
-Brad
 
Back
Top