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

message from Peter Jay Salzman on 04 Jun 2004

I'm new to Access.

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

Doug Munich replied to Peter Jay Salzman on 5 Jun 2004

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" <p@dirac.org> wrote in message
news:vU1wc.15000$Fo4.203651@typhoon.sonic.net...

hcj replied to Peter Jay Salzman on 4 Jun 2004

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

appear in any
the number of
are in:
table 2d OR ...)
describe the general
different tables?

Peter Jay Salzman replied to hcj on 04 Jun 2004

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 <anonymous@discussions.microsoft.com> wrote:

Archived message: Newbie: How to use "or" on a query? (Microsoft Access)