473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

how do i query a non active student list ?

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
6 2372
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Carl | last post by:
Please can anyone tell me how I can create a macro to save the results of a query as an excel file? The query is called Student List and I would like to save it to "My Documents". We have had...
1
by: Pieter Linden | last post by:
Hi, I think the subject line pretty much says it all... Say I have a students-classes database and I add a twist. I want to filter what courses a student can take by comparing the courses he...
2
by: Sami | last post by:
I am creating a student database. We would like to be able to look up students by ID number. New students will be input to the tables / records daily, but looking up student by ID number will be...
3
by: Sami | last post by:
Maybe I am going about this totally wrong.... What I have written is a query pulling SSN, Student-ID, LastName, FirstName, MiddleInitital from a table. Sort is so LastName, FirstName,...
0
by: Deven Oza | last post by:
Here are two sample tables of information which are used by a high school to keep track of the grades for its students. One table contains the names and addresses of the students, together with a...
4
by: kdubble | last post by:
Hi I am trying to get the results of a query to show only unique student records (not duplicates). Is there a simple way to make the criteria field do this? I am not too familiar with SQL. ...
23
by: mlcampeau | last post by:
Hey guys, I have been mulling over this problem for a few days and have yet to come up with a query that will give me the expected results. I am working on a database that stores employee...
0
by: Nyh | last post by:
I tried to explain the best I can but it is very difficult to do. Here it goes. I have three history tables. Teacher, student and school. School and teacher, and school and student are linked...
3
by: kkshansid | last post by:
i am php programmer new to asp plz help me to correct the query crt="WHERE like '%colval%" query = "SELECT . as pid, CAST(. AS TEXT)as prjtitle, Student.Title,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.