how do i query a non active student list ?

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

re: how do i query a non active student list ?

laj <jayjay@hotmail.com> wrote in
news:vevgnv4kt3shfpdv0kqckapi6s6c3ih7ee@4ax.com:
[color=blue]
> 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?[/color]

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.
[color=blue]
>
> 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?[/color]

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

re: how do i query a non active student list ?

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?

re: how do i query a non active student list ?

laj <jayjay@hotmail.com> wrote in
news:76hhnv83ku3bhb3q576rvohlba8961lb9s@4ax.com:
[color=blue]
> 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?
>[/color]
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

re: how do i query a non active student list ?

laj <jayjay@hotmail.com> wrote in message news:<76hhnv83ku3bhb3q576rvohlba8961lb9s@4ax.com>. ..[color=blue]
> 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?[/color]

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.

re: how do i query a non active student list ?

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

re: how do i query a non active student list ?

laj <jayjay@hotmail.com> wrote in message news:<bct5ov8a9u677l6fjigrihoeratv75udrm@4ax.com>. ..[color=blue]
> 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[/color]

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