Grouping Help

[bhammer]

Access2003, an Issues database.

Observations are made at Places, where Issues are noted. A fourth table
lists InspectionTypes. One junction table lists Places and corresponding
InspectionTypes that were performed at each Place. Another junction table
lists Issues and corresponding InspectionTypes during which these Issues
might be Observed. You can see that it is possible for an Issue to be
Observed at a Place where the assigned InspectionType for that Issue was not
performed. Such Observations 'don't count' and will be ignored in the report.

How do I use grouping in the report the display only those Observations of
Issues made at Places where the Issue's corresponding InspectionType was
performed? I presently have a report that groups on Issue and displays all
the Observations of that Issue, but I now want this further refinement. Can
this be done by grouping? Do I first need a new query that pulls these fields
together?

Is it odd, or a violation of db normalcy, that these six tables are related
in a circle? Like this:
tblPlaces > tblObservations > tblIssues > tblIssueDetails >
tblInspectionTypes > tblPlaceDetails > tblPlaces

-Brad

 

[Jeff Boyce]

I'm having a bit of trouble visualizing your table structure and
relationships.

If you are willing, please post back with a description along the lines of:

* one Observation can happen at many Places
* one Place can be the location of many Observations

* one Place can have many Issues
* one Issue can occur at many Places

and so on (I may not have gotten these statement correct -- please correct
them as needed)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhammer]

Jeff,

Thanks for your interest. The six tables are:
tblPlaces
PlaceID(PKey)

tblObservations
ObservID(PKey)
Place_ID(FKey)
Issue_ID(FKey)

tblIssues
IssueID(PKey)

tblIssuesDetail
IssueID(Pkey)
InspectionType_ID(PKey)

tblInspectionType
InspectionTypeID(PKey)

tblPlacesDetail
Place_ID(PKey)
InspectionType_ID(PKey)

The two Detail tables are junction tables with two Primary Keys. (Come to
think of it, I suppose the Observations table is a junction table, too, but
it has one PK.)

An Observation is a record of one Issue at one Place.
An Issue can be observed during one or more InspectionTypes.
A Place can have many Observations (ergo many Issues) and one or more
InspectionTypes.

The odd thing to me is the InspectionType being linked to Issues on the one
hand, and to Places on the other.

So I want to report a list of the Observations by Issue, where the
IspectionType is common between the Issue and the Place of the Observation.

-Brad

 

[Jeff Boyce]

Brad

Sorry, I must be dense(r) today. I'm still having trouble understanding the
relationships among your entities.

It sounds like you have Places, Issues and Inspections. Could you provide
an example of each of these?

It sounds like these are related. Could you provide an example of how?

Thanks


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhammer]

Self-solved this one. I believe the approach is similar to subqueries:

I made two queries, one on each 'side' of the lookup table
'InspectionTypes'. One query includes Observation, Address and InspectionType
from the junction table.

The other query includes Observation, Issue and InspectionType from the
other junction table.

Then using these two queries in the design grid of a third query, with two
joins (on ObservationID and on InspectionType) I can get all the Observations
records containing Issues that were observed at an Address with that Issue's
InspectionType listed.

QED