Junction Tables

[bhammer]

Is this valid?
Three tables (call them A, B & C), each with a junction table between (call
them a, b & c). Six tables total, in a related in a circle? Is there
something I'm missing with the junction tables?

Specifically, tables ABC are Addresses, Issues, and InspectionTypes.

The first Junction table 'a' lists Observations (each record has one Issue
at one Address-- an Address can have many Observations of many Issues, and
many Observations of the same Issue at the same Address).

The second junction table 'b' lists IssueDetails (each record has one Issue
and the corresponding InspectionType during which that Issue might be
observed--each record has one Issue and one InspectionType, and an Issue can
correspond with many InspectionTypes).

The third junction table 'c' lists AddressDetails (each record has an
Address and the corresponding InspectionType (one or more) that were
performed at that Address).

Should these be combined in some way? If not, what query can I write that
will return the list of Observations including only those Observations where
the Issue was observed during a corresponding InspectionType at an Address
where that InspectionType occurred? And NOT including those Observations of
Issues made at an Addrress where that Issue's corresponding InspectionType
was NOT performed? (i.e. where an Issue was Observed during a 'partial'
Inspection).

?!?

 

[Allen Browne]

Base on what you told us, the relationships make sense. The fact that they
form a circle does not present an issue (unless you do something really
weird like circular relations that cascade.)

I think you are asking how to create a query that returns all the
observations for an address, if the address has a particular observation
type. If that's the idea, you could use a subquery to identify if it has the
observation type. The subquery returns the foreign key value of record that
has this observation type. The WHERE clause of the main query then returns
all records that match this key value.

I'm not sure I've understood your structure, but perhaps something like
this:
SELECT Addresses.*, Observations.SomeField
FROM Addresses INNER JOIN Observations
ON Address.AddressID = Observations.AddressID
WHERE EXISTS
(SELECT AddressID
FROM Observations AS Obs
WHERE Obs.AddressID = Addresses.AddressID
AND Obs.InspectionType = 99);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[bhammerstrom]

Allen,

OOoo, subqueries! Something new. I think you are close. Let me try to
make clear what I want in the end. I want a report, grouped on Issue
that lists all the Observations at each Address as long as that
Observation took place during an InspectionType that is assigned to
THAT Issue. Sometimes an Issue might be observed during an inspection
that was not thorough--did not look for ALL the issues that are
possible to observe during that type of inspection--so those
observations don't count. Only those Observations of Issues where a
full Inspection of the corresponding Type occurred will count.

So if I understand subquries, I need a query that will select
Observation records where the Issue has a common InspectionType with
the Address. Seems more like a fork than a sub.

This is a wierd one.

 

[bhammerstrom]

Allen,

OOoo, subqueries! Something new. I think you are close. Let me try to
make clear what I want in the end. I want a report, grouped on Issue
that lists all the Observations at each Address as long as that
Observation took place during an InspectionType that is assigned to
THAT Issue. Sometimes an Issue might be observed during an inspection
that was not thorough--did not look for ALL the issues that are
possible to observe during that type of inspection--so those
observations don't count. Only those Observations of Issues where a
full Inspection of the corresponding Type occurred will count.

So if I understand subquries, I need a query that will select
Observation records where the Issue has a common InspectionType with
the Address. Seems more like a fork than a sub.

This is a wierd one.

 

[Allen Browne]

Yes, the subquery sounds like the way to identify the records that have the
particular inspection type you want.

If that inpsection type applied to *all* the releveant observations for the
issue, you could use use criteria in a query to achieve that. But if you
want all observations for an issue, provided one of the related records had
the particular inspection type, the subquery is the way to identify them.

 

[Jamie Collins]

Is this valid?
Three tables (call them A, B & C), each with a junction table between (call
them a, b & c). Six tables total, in a related in a circle? Is there
something I'm missing with the junction tables?

Specifically, tables ABC are Addresses, Issues, and InspectionTypes.

Rather than 'circular', think 'cyclic'. You need to take account of
direction. Do the arrows describe a cycle?

InspectionTypes seems to be a lookup table i.e. inspection type values
are attributes rather than entities. I think the correct usage of
'junction table' refers to entity tables (being the reason I prefer to
call them 'relationship tables' myself). Try (e.g. notionally)
removing the
InspectionTypes table and replacing each foreign key with an
equivalent Validation Rule (or CHECK constraint) e.g. something like:

inspection_type IN ('Full', 'Partial'))

Can you still make a circle in the Relationships window?

Jamie.

--

 

[bhammer]

Allen,

With some help from your online Tips, I got this to work. 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 AddressDetails junction table.

The other query includes Observation, Issue and InspectionType from the
IssueDetails 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

 

[bhammer]

Jamie,

Thanks for your help. Wow, this was a good puzzle, and I finally got it
today--with everyone's help!

Yes, the Relationships between the six table are in a circle, but the arrows
flow each direction starting from the InspectionTypes junciton table, and
meet at the Observations table.

If anyone is interested, I posted a few questions about this problem on the
..reports forum.

I ended up creating at least three levels of queries (sub-sub-queries?) to
get the data I needed for the Report. One of the queries has to have a
double-relationship to make it work.

It's always amazing to me how hard it is to solve these problems, them when
you look back on them a few months later you have no idea how in the world
you managed to solve it!!

With a little help, that's how. . .

thx
-Brad