By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

how do i query a non active student list ?

P: n/a
laj
HI,

Excuse this new question, I am trying to impress my wife by putting a
db together for her dance studio. I put a table with all students new
and old with fields including name and address and than a series of
fields each is a yes/no for a given offered class. On the form you
just check the class or classes they are enrolled in. My wife wants to
be able to generate a list of students (non-active) who do not have
any of the classes checked (all 'no's) , So she can send mailing just
to them when she wants. How do I set up the query?

Also, I set some reports up for her that create labels for mailing. If
we change the avery brand of labels ..how do you change the properties
of which avery brand label sheets your using once you've set it up
with label wizard?

Thanks,
Jack
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
laj <ja****@hotmail.com> wrote in
news:ve********************************@4ax.com:
HI,

Excuse this new question, I am trying to impress my wife by
putting a db together for her dance studio. I put a table with
all students new and old with fields including name and
address and than a series of fields each is a yes/no for a
given offered class. On the form you just check the class or
classes they are enrolled in. My wife wants to be able to
generate a list of students (non-active) who do not have any
of the classes checked (all 'no's) , So she can send mailing
just to them when she wants. How do I set up the query?
What you do is open the query builder, add your table and bring
down the fields you want individually by clicking on them.
In the Criteria row of the query builder, type the word No or False
under each of your class columns. Save the query. Use the query to
create your report or labels.

That said, you need to think about your table structure. having a
single table with all the classes side by side is going to be a
PITA to maintain, because every time you add a new class you will
have to redesign your queries, your forms and your reports.
Consider this alternative: Create a new table, Classes, with the
class name, teacher, maximum number of students, and such other
info as pertains to each class. Include a ClassID number.

If you don't have a StudentID number in your students table add one
there as well.

Now you create a cross-reference table, that contains two fields
StudentID and ClassID. You build queries, forms and reports that
look up student info and class info from this table.
If you want to see students with no classes, just select students
where classID is null.

Also, I set some reports up for her that create labels for
mailing. If we change the avery brand of labels ..how do you
change the properties of which avery brand label sheets your
using once you've set it up with label wizard?


You can go into the label report in design mode and tweak the label
size manually, as well as every thing else on the label, or just
run the Wizard to create a new label report. This is often faster.

Bob Q

Nov 12 '05 #2

P: n/a
laj
Thanks,
That did it...

I've done quite a few tables, queries and form for this project now
with the classes all in the same table as student reg. If a new class
(field) had to be added or dropped to the main Student table , what
would the process be to up date all the queries, forms and reports?

Nov 12 '05 #3

P: n/a
laj <ja****@hotmail.com> wrote in
news:76********************************@4ax.com:
Thanks,
That did it...

I've done quite a few tables, queries and form for this
project now with the classes all in the same table as student
reg. If a new class (field) had to be added or dropped to the
main Student table , what would the process be to up date all
the queries, forms and reports?

Open each query, form and report in design mode, analyse what
changes are required in that object, debug, save and repeat.

There's no magic tool to do this.

With the cross-reference table construction, absolutely no changes
are required. It does involve a little extra work in the original
design phase, but that's more than offset later.

Bob Q
Nov 12 '05 #4

P: n/a
laj <ja****@hotmail.com> wrote in message news:<76********************************@4ax.com>. ..
Thanks,
That did it...

I've done quite a few tables, queries and form for this project now
with the classes all in the same table as student reg. If a new class
(field) had to be added or dropped to the main Student table , what
would the process be to up date all the queries, forms and reports?


This is a classic Students-Classes database. (It might be in Access
Cookbook, I'm not sure.)

Student--(1,M)--ClassRoster--(M,1)--Class--(M,1)--Subject

If you build that way, this is a snap. "Inactive" would either be a
flag (Yes/No) value in Student table or something like:

SELECT Student.FirstName, STudent.LastName
FROM Student LEFT JOIN ClassRoster ON
Student.STudentID=ClassRoster.StudentID
WHERE ClassRoster.StudentID IS NULL
AND ClassRoster.TermStarts = #9/1/2003#;

I would swear that Access2000 has a template for this somewhere. If
you build this way, adding students, classes whatever is really easy,
and your queries, reports etc are stable.
Nov 12 '05 #5

P: n/a
laj
Thanks very much for the feedback. I stupid question for this newbie.
If i do it over using your suggestions. could you elaborate on the the
instructions you so generously provided.
1) what does the '(1,M)' refer to in the tables? (I'm not clear on the
communicated form that examples are given as I read thru the
newsgroups)
2)reg: the :"SELECT Student.FirstName, STudent.LastName
FROM Student LEFT JOIN ClassRoster ON
Student.STudentID=ClassRoster.StudentID
WHERE ClassRoster.StudentID IS NULL
AND ClassRoster.TermStarts = #9/1/2003#;"
where do you write all this. Is it in a query and just writing this
as an expression after picking 'expression' from the 'group by'
summation function?

Jack
Nov 12 '05 #6

P: n/a
laj <ja****@hotmail.com> wrote in message news:<bc********************************@4ax.com>. ..
Thanks very much for the feedback. I stupid question for this newbie.
If i do it over using your suggestions. could you elaborate on the the
instructions you so generously provided.
1) what does the '(1,M)' refer to in the tables? (I'm not clear on the
communicated form that examples are given as I read thru the
newsgroups)
2)reg: the :"SELECT Student.FirstName, STudent.LastName
FROM Student LEFT JOIN ClassRoster ON
Student.STudentID=ClassRoster.StudentID
WHERE ClassRoster.StudentID IS NULL
AND ClassRoster.TermStarts = #9/1/2003#;"
where do you write all this. Is it in a query and just writing this
as an expression after picking 'expression' from the 'group by'
summation function?

Jack


Copy and paste this into the QBE grid. Open a new query. Click the
SQL button (top left). Paste. Of course, this kind of assumes that
your tables are named as mine are...

HTH,
Pieter
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.