How to build a "reach around" query?

[WDSnews]

If my tables are properly normalized, how can I build a query in which a
table with multiple relationships can correctly join if the daughter and
grand-daughter tables only contain a single linking field and the
great-grand-daughter contains the two distinguishing fields.... for example.

The [Attendance] table contains a record for each month a student has
attendance. Its fields include [Student ID], [Month ID], and [School ID].
The data that applies to a particular school is identified by the [School
ID] field.

The [Month] table contains data specified by the State and applies to all
schools. Its fields include [Max Days] and [Qtr ID]. The [Quarter] table
also contains data specified by the State and applies to all schools. Its
fields include [cumMax Days]. The [School Year] table contains one record
per year per school and is customized to the school. However, data in a
particular record is specified by the State and unique to the school, such
as [Half Year] in which the date of the 88th school day is specified.

In this query the [Attendance] table is the parent table. Its [Month ID]
links to [Month].[ID]. Then [Month].[Qtr ID] links to [Quarter].[ID]. Then
[Quarter].[Year] links to [School Year].[Year]. However with multiple
[Year] records, I need [Attendance].[School ID] to link to [School
Year].[School ID].

I want the query to render one record for each record in the Attendance
table. Unfortunately, Access says I have ambiguous outer joins. What is
the correct way to do this without breaking normalization rules or
unnecessarily duplicating records? The simple solution seems to be adding a
[Year] field to the Attendance table, but that breaks normalization rules
since it already contains a [Month ID] field.

 

[Rob Parker]

Not sure if this will give exactly what you want, but it seems to work in a
limited test for me.

Join your four tables as you describe for
parent-daughter-granddaughter-greatgranddaughter. Do not join
[Attendance].[SchoolID] to [School Year].[SchoolID]. Instead, include the
field [School Year].[SchoolID] in the query (set it to not show if you don't
want to see it), and set a criterion on that field of
=[Attendance].[SchoolID]

HTH,

Rob

 

[KARL DEWEY]

The [Attendance] table contains a record for each month a student has
attendance.
Does that mean that a student could actually attend only one day in a given
month and the school get credit for full-time attendance? No daily
attendance record?

 

[WDSnews]

Thank you. I believe that solution would work and I have another
application where it will help me. However, for data model reasons, I tried
Ken's second suggestion on this issue, using a subquery.

Not sure if this will give exactly what you want, but it seems to work in
a limited test for me.

Join your four tables as you describe for
parent-daughter-granddaughter-greatgranddaughter. Do not join
[Attendance].[SchoolID] to [School Year].[SchoolID]. Instead, include the
field [School Year].[SchoolID] in the query (set it to not show if you
don't want to see it), and set a criterion on that field of
=[Attendance].[SchoolID]

HTH,

Rob

If my tables are properly normalized, how can I build a query in
which a table with multiple relationships can correctly join if the
daughter and grand-daughter tables only contain a single linking
field and the great-grand-daughter contains the two distinguishing
fields.... for example.
The [Attendance] table contains a record for each month a student has
attendance. Its fields include [Student ID], [Month ID], and [School
ID]. The data that applies to a particular school is identified by
the [School ID] field.

The [Month] table contains data specified by the State and applies to
all schools. Its fields include [Max Days] and [Qtr ID]. The
[Quarter] table also contains data specified by the State and applies
to all schools. Its fields include [cumMax Days]. The [School Year]
table contains one record per year per school and is customized to
the school. However, data in a particular record is specified by the
State and unique to the school, such as [Half Year] in which the date
of the 88th school day is specified.
In this query the [Attendance] table is the parent table. Its [Month
ID] links to [Month].[ID]. Then [Month].[Qtr ID] links to
[Quarter].[ID]. Then [Quarter].[Year] links to [School Year].[Year].
However with multiple [Year] records, I need [Attendance].[School ID]
to link to [School Year].[School ID].

I want the query to render one record for each record in the
Attendance table. Unfortunately, Access says I have ambiguous outer
joins. What is the correct way to do this without breaking
normalization rules or unnecessarily duplicating records? The simple
solution seems to be adding a [Year] field to the Attendance table,
but that breaks normalization rules since it already contains a
[Month ID] field.

 

[WDSnews]

No.

The [Attendance] table contains a record for each month a student has

attendance.
Does that mean that a student could actually attend only one day in a
given
month and the school get credit for full-time attendance? No daily
attendance record?

--
Build a little, test a little.

If my tables are properly normalized, how can I build a query in which a
table with multiple relationships can correctly join if the daughter and
grand-daughter tables only contain a single linking field and the
great-grand-daughter contains the two distinguishing fields.... for
example.

The [Attendance] table contains a record for each month a student has
attendance. Its fields include [Student ID], [Month ID], and [School
ID].
The data that applies to a particular school is identified by the [School
ID] field.

The [Month] table contains data specified by the State and applies to all
schools. Its fields include [Max Days] and [Qtr ID]. The [Quarter]
table
also contains data specified by the State and applies to all schools.
Its
fields include [cumMax Days]. The [School Year] table contains one
record
per year per school and is customized to the school. However, data in a
particular record is specified by the State and unique to the school,
such
as [Half Year] in which the date of the 88th school day is specified.

In this query the [Attendance] table is the parent table. Its [Month ID]
links to [Month].[ID]. Then [Month].[Qtr ID] links to [Quarter].[ID].
Then
[Quarter].[Year] links to [School Year].[Year]. However with multiple
[Year] records, I need [Attendance].[School ID] to link to [School
Year].[School ID].

I want the query to render one record for each record in the Attendance
table. Unfortunately, Access says I have ambiguous outer joins. What is
the correct way to do this without breaking normalization rules or
unnecessarily duplicating records? The simple solution seems to be
adding a
[Year] field to the Attendance table, but that breaks normalization rules
since it already contains a [Month ID] field.




.

 

[WDSnews]

I don't have enough experience with SQL queries to have a strong opinion
here. I tried your nested query suggestion and it worked for me.

I'm using a variation on this query to collect multiple month records in a
summary query. I'm using 'group by', expression, 'sum' and 'max'. Since
the parent table is Attendance and records there imply daughter records down
stream, I suppose I could still use inner joins for this?

Why are you using outer joins? As far as I can see you should be able to
use
inner joins as there should be no unmatched rows in any of the referencing
tables; referential integrity would require a row to exist in the Month
table
with the necessary Month ID value before a row can be inserted into
Attendance; similarly a row with the necessary Qtr ID would be required in
the Quarter table before a row can be inserted in Month. These
constraints
are easily enforced of course.

The only possible fly in the ointment would be if for there were no
matching
row in the School Year table with the necessary Year and SchoolID values
for
any of the rows returned by joining the other three tables. If this were
the
case then Rob's solution would not work as it in effect transfers the JOIN
between Attendance and School Year to the WHERE clause (which is how joins
were effected prior to the SQL 92 standard in fact). Joining by a
criterion
in the WHERE clause is the equivalent of an INNER JOIN, however, so if
there
were rows in Attendance without a match in School Year for the year in
question they would not be returned. If on the other hand there are no
unmatched rows it would work, but does not serve any purpose as an INNER
JOIN
would be possible.

If there really are rows in Attendance without a match in School Year for
the
school/year in question, thus requiring an OUTER JOIN to return rows from
the
former, then I think the only way you'd be able to do this would be to
first
create a query which INNER JOINs Attendance, Month and Quarter, and then
OUTER JOIN this in another query to School Year on the SchoolID and Year
columns. I'd be surprised if this is necessary, though, as I'd have
thought
that if a row exists for a particular school/month in Attendance, then a
row
for the school/year in which that month occurs should exist in School
Year,
notwithstanding the fact that this cannot be enforced in your current
model.

BTW I'd recommend against using Month as a table name as its the name of a
built in function. I personally favour plural or collective nouns as
table
names, e.g. Months, Quarters and SchoolYears as they better represent the
fact that tables are sets. Similarly I favours singular nouns as column
names as these each represent an Attribute type.

Ken Sheridan
Stafford, England

If my tables are properly normalized, how can I build a query in which a
table with multiple relationships can correctly join if the daughter and
grand-daughter tables only contain a single linking field and the
great-grand-daughter contains the two distinguishing fields.... for
example.

The [Attendance] table contains a record for each month a student has
attendance. Its fields include [Student ID], [Month ID], and [School ID].
The data that applies to a particular school is identified by the [School
ID] field.

The [Month] table contains data specified by the State and applies to all
schools. Its fields include [Max Days] and [Qtr ID]. The [Quarter] table
also contains data specified by the State and applies to all schools. Its
fields include [cumMax Days]. The [School Year] table contains one record
per year per school and is customized to the school. However, data in a
particular record is specified by the State and unique to the school, such
as [Half Year] in which the date of the 88th school day is specified.

In this query the [Attendance] table is the parent table. Its [Month ID]
links to [Month].[ID]. Then [Month].[Qtr ID] links to [Quarter].[ID].
Then
[Quarter].[Year] links to [School Year].[Year]. However with multiple
[Year] records, I need [Attendance].[School ID] to link to [School
Year].[School ID].

I want the query to render one record for each record in the Attendance
table. Unfortunately, Access says I have ambiguous outer joins. What is
the correct way to do this without breaking normalization rules or
unnecessarily duplicating records? The simple solution seems to be adding
a
[Year] field to the Attendance table, but that breaks normalization rules
since it already contains a [Month ID] field.