Query Problem

[Guest]

I am trying to perform a query from four tables. I have one column from each
table in the query. The columns represent registrant catagories: summer,
winter, and fall, and the primary key is the student #. (Summer, winter, and
fall, come from different tables and choices in one column)

I want the query to list every student and the season participated in. But,
when I run the query, I only get students that registered for every season,
summer, winter, and fall. That is, I can't get the query to list registrants
that only participated in one season, such as winter. The query insists on
only listing the records in which the student participated in every season.
Is there any way to make the query list all students, not just the ones who
participated in every season?

 

[Jeff Boyce]

Putting "summer", "winter" and "fall" student data into different tables
sounds like ... a spreadsheet! If you do that in a relational database
(e.g., Access), you will not get the best use of the available
features/functions.

I assume that the fields in the three tables are the same.

Instead of using separate tables for each season (and, I assume, for
separate years!), copy one of those tables, then add a [Season] field (and
if you need to do this for more than one year, add an [AcademicYear] field
too).

Consider posting a more complete description of your underlying data -- the
newsgroup may be able to offer alternate approaches.

Have you searched on-line for "student" or "education"-oriented database
models?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I was mistaken when I typed that the student ID is the primary field; in
fact, the primary field is camp number; then, each season lists the number of
students in camp (camp x, for example) for that season. I still would like
the querry to list all the data for a particular camp number. If, for
example, Camp X had students summer, winter, but not fall, I would like to
see that.

Can Access do that?

Putting "summer", "winter" and "fall" student data into different tables
sounds like ... a spreadsheet! If you do that in a relational database
(e.g., Access), you will not get the best use of the available
features/functions.

I assume that the fields in the three tables are the same.

Instead of using separate tables for each season (and, I assume, for
separate years!), copy one of those tables, then add a [Season] field (and
if you need to do this for more than one year, add an [AcademicYear] field
too).

Consider posting a more complete description of your underlying data -- the
newsgroup may be able to offer alternate approaches.

Have you searched on-line for "student" or "education"-oriented database
models?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to perform a query from four tables. I have one column from
each
table in the query. The columns represent registrant catagories: summer,
winter, and fall, and the primary key is the student #. (Summer, winter,
and
fall, come from different tables and choices in one column)

I want the query to list every student and the season participated in.
But,
when I run the query, I only get students that registered for every
season,
summer, winter, and fall. That is, I can't get the query to list
registrants
that only participated in one season, such as winter. The query insists on
only listing the records in which the student participated in every
season.
Is there any way to make the query list all students, not just the ones
who
participated in every season?

 

[Jeff Boyce]

It sounds like you are describing a spreadsheet more than a relational
database.

You can "force" Access to do a number of things, but that doesn't mean it's
a good idea, nor easy.

Take another look at the structure of your data, and look at how Access
handles "crosstab" queries and reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was mistaken when I typed that the student ID is the primary field; in
fact, the primary field is camp number; then, each season lists the number
of
students in camp (camp x, for example) for that season. I still would like
the querry to list all the data for a particular camp number. If, for
example, Camp X had students summer, winter, but not fall, I would like to
see that.

Can Access do that?

Putting "summer", "winter" and "fall" student data into different tables
sounds like ... a spreadsheet! If you do that in a relational database
(e.g., Access), you will not get the best use of the available
features/functions.

I assume that the fields in the three tables are the same.

Instead of using separate tables for each season (and, I assume, for
separate years!), copy one of those tables, then add a [Season] field
(and
if you need to do this for more than one year, add an [AcademicYear]
field
too).

Consider posting a more complete description of your underlying data --
the
newsgroup may be able to offer alternate approaches.

Have you searched on-line for "student" or "education"-oriented database
models?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to perform a query from four tables. I have one column from
each
table in the query. The columns represent registrant catagories:
summer,
winter, and fall, and the primary key is the student #. (Summer,
winter,
and
fall, come from different tables and choices in one column)

I want the query to list every student and the season participated in.
But,
when I run the query, I only get students that registered for every
season,
summer, winter, and fall. That is, I can't get the query to list
registrants
that only participated in one season, such as winter. The query insists
on
only listing the records in which the student participated in every
season.
Is there any way to make the query list all students, not just the ones
who
participated in every season?