Newbie: How to use "or" on a query?

I'm new to Access.

I have a bunch of tables:

1. Enrollments for Fall 2003
2a. Graduations for Spring 2004
2b. Graduations for Summer 2004
2c. Graduations for Fall 2004
2d. Graduations for Spring 2005
2e. (and so on)

Each table has a student's social security number (SSN).

I'd like to find the number of SSN's in table 1 that also appear in any
one of the other tables. In other words, I want to know the number of
people who enrolled in Fall 2003 and graduated.

In other words, I want to know the number of people who are in:

table 1 AND ( table 2a OR table 2b OR table 2c OR table 2d OR ...)

I have no clue how to do this. Can someone please describe the general
procedure of how to do "or-ing" among elements in different tables?

Any help greatly appreciated.

Thanks!
Pete

This looks like a job for an append query - i.e. you'll
need to create a results table for the records you find in
each of the graduation tables, which you then append into
the results table. I suggest this approach because it
looks as though (a) you only need to search each
graduation table one time, (b) the job is open ended until
all the enrollees have graduated, dropped out, or are
otherwise no longer in the system, and (c) it looks like
you might want to perform this task for enrollment classes
yet to come.
If these ideas are not enough to get you rolling, post
back and I'll expand a bit. If this really is an open-
ended, long term task, I might suggest you redesign your
class enrollment table(s) to hold graduation stats later
on via an update query.

Looking for your reply. hcj

-----Original Message-----
I'm new to Access.

I have a bunch of tables:

1. Enrollments for Fall 2003
2a. Graduations for Spring 2004
2b. Graduations for Summer 2004
2c. Graduations for Fall 2004
2d. Graduations for Spring 2005
2e. (and so on)

Each table has a student's social security number (SSN).

I'd like to find the number of SSN's in table 1 that also
appear in any
one of the other tables. In other words, I want to know
the number of
people who enrolled in Fall 2003 and graduated.

In other words, I want to know the number of people who
are in:

table 1 AND ( table 2a OR table 2b OR table 2c OR
table 2d OR ...)

I have no clue how to do this. Can someone please
describe the general
procedure of how to do "or-ing" among elements in
different tables?

Any help greatly appreciated.

Thanks!
Pete
.

Hi HCJ,

I was able to get the job done using "outer joins", which I've never
heard of before. He gave a recipe so I got my answer, but I don't
really know the "why or how" of what I did. I'm definitely going to
read up on outer joins this weekend.

I also don't know what an append query is, so that's going on my list of
items to read this weekend too. I like having multiple ways of doing
things.

A co-worker tried helping me before we both gave up, and she mentioned
that she thought "append queries" were the way to go, so obviously, this
is something that even a basic Access user should know.

I just bought "The Complete Reference, MS Office Access 2003" by Virgina
Andersen, and it appears to be a decent book, and it looks like it goes
into append queries.

Thanks for the tip! I really appreciate it!

Pete

hcj wrote:
This looks like a job for an append query - i.e. you'll
need to create a results table for the records you find in
each of the graduation tables, which you then append into
the results table. I suggest this approach because it
looks as though (a) you only need to search each
graduation table one time, (b) the job is open ended until
all the enrollees have graduated, dropped out, or are
otherwise no longer in the system, and (c) it looks like
you might want to perform this task for enrollment classes
yet to come.
If these ideas are not enough to get you rolling, post
back and I'll expand a bit. If this really is an open-
ended, long term task, I might suggest you redesign your
class enrollment table(s) to hold graduation stats later
on via an update query.

Looking for your reply. hcj

-----Original Message-----
I'm new to Access.

I have a bunch of tables:

1. Enrollments for Fall 2003
2a. Graduations for Spring 2004
2b. Graduations for Summer 2004
2c. Graduations for Fall 2004
2d. Graduations for Spring 2005
2e. (and so on)

Each table has a student's social security number (SSN).

I'd like to find the number of SSN's in table 1 that also
appear in any
one of the other tables. In other words, I want to know
the number of
people who enrolled in Fall 2003 and graduated.

In other words, I want to know the number of people who
are in:

table 1 AND ( table 2a OR table 2b OR table 2c OR
table 2d OR ...)

I have no clue how to do this. Can someone please
describe the general
procedure of how to do "or-ing" among elements in
different tables?

Any help greatly appreciated.

Thanks!
Pete
.

On the query design grid you can put a different set of criteria on each row
and they are ORed, so on the first row you have table1 and table 2, on the
second table 1 and table3, etc.

Doug

"Peter Jay Salzman" wrote in message
...
I'm new to Access.

I have a bunch of tables:

1. Enrollments for Fall 2003
2a. Graduations for Spring 2004
2b. Graduations for Summer 2004
2c. Graduations for Fall 2004
2d. Graduations for Spring 2005
2e. (and so on)

Each table has a student's social security number (SSN).

I'd like to find the number of SSN's in table 1 that also appear in any
one of the other tables. In other words, I want to know the number of
people who enrolled in Fall 2003 and graduated.

In other words, I want to know the number of people who are in:

table 1 AND ( table 2a OR table 2b OR table 2c OR table 2d OR ...)

I have no clue how to do this. Can someone please describe the general
procedure of how to do "or-ing" among elements in different tables?

Any help greatly appreciated.

Thanks!
Pete

Hi Peter,

Your task would be much easier if the database was designed properly. You really don't want
separate tables for storing this information. Offhand, it appears as if a single table will do
the job. A single table could include fields for SSN (text), FirstName (text), LastName (text),
EnrollDate (Date/Time), and GradDate (Date/Time). I have used the American format for dates in
this example instead of a string value such as "Fall 2003". If you store actual dates, you'll be
able to perform calculations that involve dates a lot easier, in order to answer questions such
as "What is the average enrollment time, in days, for all students who graduated"? An example of
the data might look something like this:

Table: tblEnrollments

SSN FirstName LastName EnrollDate GradDate
123-45-6789 Mickey Mouse 9/1/2003 6/10/2004
987-65-4321 Minnie Mouse 9/1/2003 8/15/2004
111-22-3333 Donald Duck 9/1/2003 12/20/2004
222-33-4444 Porky Pig 1/2/2004
etc.

A simple SELECT query would then yield the results you want. The SQL (Structured Query Language)
statement might look something like this:

SELECT Count(SSN) AS [Number Graduates]
FROM tblEnrollments
WHERE EnrollDate = #9/1/2003# AND GradDate IS NOT NULL;

This query would return 3 for the data listed above. A good book for learning SQL is ""SQL
Queries for Mere Mortals", written by John Viescas and Michael Hernandez.
http://www.datatexcg.com/For%20Mere%...als/sql4mm.htm


Here is one site that you can start at for learning Access:
http://www.simply-access.com/

You should spend some time gaining an understanding of database design and normalization before
attempting to build something in Access (or any RDBMS software for that matter). Here are some
links to get you started. Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of coffee and enjoy reading!

http://www.datatexcg.com/Downloads/D...gnTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

Since you are new at this game, now would be a good time to commit yourself to using naming
conventions. They're not that hard to learn. You will be able to avoid errors that others
encounter if you use naming conventions.
http://www.mvps.org/access/general/gen0012.htm

Also recommended: Find the copy of Northwind.mdb that is probably already installed on your hard
drive. Study the relationships between the various tables (Tools Relationships...)


Tom
______________________________________

"Peter Jay Salzman" wrote in message
...

I'm new to Access.

I have a bunch of tables:

1. Enrollments for Fall 2003
2a. Graduations for Spring 2004
2b. Graduations for Summer 2004
2c. Graduations for Fall 2004
2d. Graduations for Spring 2005
2e. (and so on)

Each table has a student's social security number (SSN).

I'd like to find the number of SSN's in table 1 that also appear in any
one of the other tables. In other words, I want to know the number of
people who enrolled in Fall 2003 and graduated.

In other words, I want to know the number of people who are in:

table 1 AND ( table 2a OR table 2b OR table 2c OR table 2d OR ...)

I have no clue how to do this. Can someone please describe the general
procedure of how to do "or-ing" among elements in different tables?

Any help greatly appreciated.

Thanks!
Pete