Non-equal theta join?

[tdp]

Hi. I have three tables: Tutor02-03, Tutor03-04, Tutor04-05, each with a
corresponding field called InstructionalHoursxx-xx (where xx-xx corresponds
to the same year of the table, ex. InstructionalHours02-03).

I need to find the total hours over the three years for all the tutors. Some
of the tutors appear in all three tables, some in two, and some in only one.
Each tutor is identified by a unique 4-digit ID number which is the same in
any table in which they appear. The tables are related by this field (Access
did that automatically).

I have no problem getting a calculated field called TotalHours for the
tutors that appear in all three tables, but how can I pull out the
information for those tutors who only appear in one or two tables?

For example, I tried a query to exclude those tutors who were in BOTH 02-03
AND 03-04 by having the Last_Name field from each table in the query, and
then putting the criterion <>[Tutor03-04]![Last_Name] in the
[Tutor02-03].[Last_Name] field--that didn't work (I got no records returned).
I also tried the same criterion using Not-- Not"[Tutor03-04]![Last_Name]"
(since Last_Name is a string)--that returned all the records in the 02-03
table (why??)

BTW, I only tried this after trying it with the ID numbers, figuring numbers
might make easier criteria than text strings, but it didn't work with the ID
numbers either.

I thought if I could get these queries to work (i.e. writing individual
queries for all the possibilities--tutors in three tables, tutors in two
tables, tutors in only one of the tables), that I could then write one last
query to pull them all together and have a TotalHours calculated fields (I
know to use nz since some of the fields will be null).

I hope this is understandable. Can anyone help?

 

[Gerald Stanley]

I would recommend that you use a summation query of a union query e.g.
something along the lines of

SELECT tutorId, SUM(instructionalHours) FROM (
SELECT tutorId, instructionalHours02-03 AS instructionalHours FROM Tutor02-03
UNION ALL
SELECT tutorId, instructionalHours03-04 FROM Tutor03-04
UNION ALL
SELECT tutorId, instructionalHours04-05 FROM Tutor04-05)
GROUP BY tutorId

I would also recommend a more normalised database design where all this
information is held in one table with an extra column to denote the
applicable year.

Hope This Helps
Gerald Stanley MCSD

 

[Jeff Boyce]

Your data design is unnecessarily complex, and more closely resembles a
spreadsheet. There is no reason to use separate tables for separate years'
data in Access (and, as you are finding, some very good reasons not to).

You could use a single table holding the same information you now spread
across three tables (?!4 once the next year passes; 5 the year after that;
.... -- ?!are you going to rewrite your query, forms and reports every
year?!). Plus one more field -- a "InstructionalYear" field.

This revised design allows you to look for (query) all tutors, any year(s),
in a single query.

I'd urge you to consider redesigning your data structure to take better
advantage of the features and functions that Access offers. Otherwise, why
not just use a spreadsheet?

 

[Guest]

I did not design these tables; they are downloaded from a
centralized state data reporting system. The data is
closed and frozen at the end of each program year, which
is why there is a different table for each year.

I am just trying to work with what I have been given <g>!

tdp

-----Original Message-----
Your data design is unnecessarily complex, and more closely resembles a
spreadsheet. There is no reason to use separate tables for separate years'
data in Access (and, as you are finding, some very good reasons not to).

You could use a single table holding the same information you now spread
across three tables (?!4 once the next year passes; 5 the year after that;
.... -- ?!are you going to rewrite your query, forms and reports every
year?!). Plus one more field -- a "InstructionalYear" field.

This revised design allows you to look for (query) all tutors, any year(s),
in a single query.

I'd urge you to consider redesigning your data structure to take better
advantage of the features and functions that Access offers. Otherwise, why
not just use a spreadsheet?

--
Good luck

Jeff Boyce
<Access MVP>

Hi. I have three tables: Tutor02-03, Tutor03-04, Tutor04-05, each with a
corresponding field called InstructionalHoursxx-xx

(where xx-xx
corresponds

to the same year of the table, ex. InstructionalHours02-03).

I need to find the total hours over the three years

for all the tutors.
Some

of the tutors appear in all three tables, some in two,

and some in only
one.

Each tutor is identified by a unique 4-digit ID number

which is the same
in

any table in which they appear. The tables are related

by this field
(Access

did that automatically).

I have no problem getting a calculated field called TotalHours for the
tutors that appear in all three tables, but how can I pull out the
information for those tutors who only appear in one or two tables?

For example, I tried a query to exclude those tutors

who were in BOTH
02-03

AND 03-04 by having the Last_Name field from each table in the query, and
then putting the criterion <>[Tutor03-04]![Last_Name] in the
[Tutor02-03].[Last_Name] field--that didn't work (I

got no records
returned).

I also tried the same criterion using Not-- Not"[Tutor03-04]![Last_Name]"
(since Last_Name is a string)--that returned all the records in the 02-03
table (why??)

BTW, I only tried this after trying it with the ID

numbers, figuring
numbers

might make easier criteria than text strings, but it

didn't work with the
ID

numbers either.

I thought if I could get these queries to work (i.e. writing individual
queries for all the possibilities--tutors in three tables, tutors in two
tables, tutors in only one of the tables), that I

could then write one
last

query to pull them all together and have a TotalHours calculated fields (I
know to use nz since some of the fields will be null).

I hope this is understandable. Can anyone help?

.

 

[Jeff Boyce]

No reason you couldn't load the downloads to a different, more
Access-useable structure...

--
Good luck

Jeff Boyce
<Access MVP>

I did not design these tables; they are downloaded from a
centralized state data reporting system. The data is
closed and frozen at the end of each program year, which
is why there is a different table for each year.

I am just trying to work with what I have been given <g>!

tdp

-----Original Message-----
Your data design is unnecessarily complex, and more closely resembles a
spreadsheet. There is no reason to use separate tables for separate years'
data in Access (and, as you are finding, some very good reasons not to).

You could use a single table holding the same information you now spread
across three tables (?!4 once the next year passes; 5 the year after that;
.... -- ?!are you going to rewrite your query, forms and reports every
year?!). Plus one more field -- a "InstructionalYear" field.

This revised design allows you to look for (query) all tutors, any year(s),
in a single query.

I'd urge you to consider redesigning your data structure to take better
advantage of the features and functions that Access offers. Otherwise, why
not just use a spreadsheet?

--
Good luck

Jeff Boyce
<Access MVP>

Hi. I have three tables: Tutor02-03, Tutor03-04, Tutor04-05, each with a
corresponding field called InstructionalHoursxx-xx

(where xx-xx
corresponds

to the same year of the table, ex. InstructionalHours02-03).

I need to find the total hours over the three years

for all the tutors.
Some

of the tutors appear in all three tables, some in two,

and some in only
one.

Each tutor is identified by a unique 4-digit ID number

which is the same
in

any table in which they appear. The tables are related

by this field
(Access

did that automatically).

I have no problem getting a calculated field called TotalHours for the
tutors that appear in all three tables, but how can I pull out the
information for those tutors who only appear in one or two tables?

For example, I tried a query to exclude those tutors

who were in BOTH
02-03

AND 03-04 by having the Last_Name field from each table in the query, and
then putting the criterion <>[Tutor03-04]![Last_Name] in the
[Tutor02-03].[Last_Name] field--that didn't work (I

got no records
returned).

I also tried the same criterion using Not-- Not"[Tutor03-04]![Last_Name]"
(since Last_Name is a string)--that returned all the records in the 02-03
table (why??)

BTW, I only tried this after trying it with the ID

numbers, figuring
numbers

might make easier criteria than text strings, but it

didn't work with the
ID

numbers either.

I thought if I could get these queries to work (i.e. writing individual
queries for all the possibilities--tutors in three tables, tutors in two
tables, tutors in only one of the tables), that I

could then write one
last

query to pull them all together and have a TotalHours calculated fields (I
know to use nz since some of the fields will be null).

I hope this is understandable. Can anyone help?

.

 

[tdp]

That works absolutely perfectly; thank you SO MUCH!!

I did not design these tables; they are from a
centralized state database where the data is frozen and
closed at the end of each program year, hence the three
tables for the three different program years. The tables
are used in both Access and Excel--there is a lot more
you can do with them in Access for our purposes, which is
why I was trying to solve this problem in Access.

Thanks again for your help!
tdp

-----Original Message-----
I would recommend that you use a summation query of a union query e.g.
something along the lines of

SELECT tutorId, SUM(instructionalHours) FROM (
SELECT tutorId, instructionalHours02-03 AS

instructionalHours FROM Tutor02-03

UNION ALL
SELECT tutorId, instructionalHours03-04 FROM Tutor03-04
UNION ALL
SELECT tutorId, instructionalHours04-05 FROM Tutor04-05)
GROUP BY tutorId

I would also recommend a more normalised database design where all this
information is held in one table with an extra column to denote the
applicable year.

Hope This Helps
Gerald Stanley MCSD

Hi. I have three tables: Tutor02-03, Tutor03-04, Tutor04-05, each with a
corresponding field called InstructionalHoursxx-xx (where xx-xx corresponds
to the same year of the table, ex. InstructionalHours02-03).

I need to find the total hours over the three years for all the tutors. Some
of the tutors appear in all three tables, some in two, and some in only one.
Each tutor is identified by a unique 4-digit ID number which is the same in
any table in which they appear. The tables are related by this field (Access
did that automatically).

I have no problem getting a calculated field called TotalHours for the
tutors that appear in all three tables, but how can I pull out the
information for those tutors who only appear in one or two tables?

For example, I tried a query to exclude those tutors who were in BOTH 02-03
AND 03-04 by having the Last_Name field from each table in the query, and
then putting the criterion <>[Tutor03-04]![Last_Name] in the
[Tutor02-03].[Last_Name] field--that didn't work (I got no records returned).
I also tried the same criterion using Not-- Not"[Tutor03-04]![Last_Name]"
(since Last_Name is a string)--that returned all the records in the 02-03
table (why??)

BTW, I only tried this after trying it with the ID numbers, figuring numbers
might make easier criteria than text strings, but it didn't work with the ID
numbers either.

I thought if I could get these queries to work (i.e. writing individual
queries for all the possibilities--tutors in three tables, tutors in two
tables, tutors in only one of the tables), that I could then write one last
query to pull them all together and have a TotalHours calculated fields (I
know to use nz since some of the fields will be null).

I hope this is understandable. Can anyone help?

.