Record selection based on multipule records

[gordyn5]

I have two tables
1) Students: Name, Address etc.
2) Classes: Name, Class Title
The two tables are related with the Name
I need to find out if a Student has taken 4 Classes to qualify for the 5th.

For example, the Student has to have taken Algebra I, Algebra II, Calc I and
Calc II before taking Physics.

How do I query the Classes table with the Class Title to get the names of
the Students who qualify to take Physics?

 

[Allen Browne]

There's a great deal more required here to get this to work.

Over time, you repeat a subject (such as Algebra I) several times. You
therefore need a table of Subjects, as well as Classes, with a one-to-many
relation (since once subject is repeated in many classes.)

Now you need a table to define the subjects that are prerequisites for a
subject. It will have fields like this:
- SubjectID what subject needs the prerequisite.
- PriorSubjectID what subject is the prerequisite for the SubjectID.
In the Relationships window, you will need 2 copies of the Subjects table.
Access will alias the 2nd one as Subject_1.

You can now create two relationships:
- Subjects.SubjectID to SubjectsPriorsubject.SubjectID
- Subjects_1.SubjectID to SubjectsPriorsubject.PriorSubjectID

Now, your Classes table needs fields such as:
ClassID AutoNumber primary key
SubjectID Number which subject this is
StartDate Date/Time the date (or semester) this class starts
LecturerID Number who is supervising this course.

The students don't go in the Classes table. Since one class contains many
students, you need another table to record who's in what classe. This
ClassStudent table will have fields like this:
ClassID Number which class this row is for
StudentID Number which student is in this class.

You will also need some way of recording the classes the student has passed.

Now you need some code in the Form_BeforeUpdate event procedure of the form
where you enter students into classes. Your code can then look up the
subject the code is for, the prerequisites for the subject, and see if any
of them is missing from the classes the student has passed.

That's the core idea. There are also heaps of other aspects, such as the
assessments for the class, the assignments submitted by each student (don't
forget the possibilty of resubmits), grading criteria, attendance at
classes, and so on. Oh, and enrolling in a course is a whole other area (in
addition to the actual classes the students are taking.)

Finally, it would be good to avoid problem field names like Name and Class:
Access will misunderstand Name (to be the name of the form or report,
instead of the contents of the Name field), and Class is a future-reserved
word for SQL Server. Here's an extensive list of the names not to use:
http://allenbrowne.com/AppIssueBadWord.html

 

[Tom van Stiphout]

On Wed, 17 Sep 2008 19:53:01 -0700, gordyn5

You mean you have a referential integrity link between Students.Name
and Classes.Name? You may want to rethink that before you turn in
your homework.

-Tom.
Microsoft Access MVP

 

[triton]

Not really
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")

myval = rs![Class Title]
will work
But, I do agree with your naming conventions.

If you really want to get old school with it, table field names would be
all upper case with word seperated by underscores:
CLASS_TITLE

But Camel Case has become more popular these days

ClassTitle

Who named the fields? My rule of thumb for beginning database
designers is:
Do not use any punctuation or spaces in field names.
Do not use dictionary words as field names.

Of course, if someone else named the fields, you may have no choice,
but the problem with using dictionary words as field names is that all
the good ones - like 'name' and 'date' - have already been used, and
will cause conflicts later.

The problem with spaces in your field names is that they make everything
slightly more difficult: you can't use
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")
and you can't use
SELECT Class Title FROM mytable
you have to use
SELECT [Class Title] FROM mytable

The display name does not have to be the same as the field name. You
can change the display name on forms, reports and queries independently
from the field name.

A good way to start learning SQL is to create your queries in the Access
"Query By Example" query design window, then switch to "SQL View"
to see the SQL that has been created.

(david)

I have two tables
1) Students: Name, Address etc.
2) Classes: Name, Class Title
The two tables are related with the Name
I need to find out if a Student has taken 4 Classes to qualify for the
5th.

For example, the Student has to have taken Algebra I, Algebra II, Calc I
and
Calc II before taking Physics.

How do I query the Classes table with the Class Title to get the names
of
the Students who qualify to take Physics?

 

[david]

Who named the fields? My rule of thumb for beginning database
designers is:
Do not use any punctuation or spaces in field names.
Do not use dictionary words as field names.

Of course, if someone else named the fields, you may have no choice,
but the problem with using dictionary words as field names is that all
the good ones - like 'name' and 'date' - have already been used, and
will cause conflicts later.

The problem with spaces in your field names is that they make everything
slightly more difficult: you can't use
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")
and you can't use
SELECT Class Title FROM mytable
you have to use
SELECT [Class Title] FROM mytable

The display name does not have to be the same as the field name. You
can change the display name on forms, reports and queries independently
from the field name.

A good way to start learning SQL is to create your queries in the Access
"Query By Example" query design window, then switch to "SQL View"
to see the SQL that has been created.

(david)

 

[Klatuu]

Not really
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")

myval = rs![Class Title]
will work
But, I do agree with your naming conventions.

If you really want to get old school with it, table field names would be all
upper case with word seperated by underscores:
CLASS_TITLE

But Camel Case has become more popular these days

ClassTitle

Who named the fields? My rule of thumb for beginning database
designers is:
Do not use any punctuation or spaces in field names.
Do not use dictionary words as field names.

Of course, if someone else named the fields, you may have no choice,
but the problem with using dictionary words as field names is that all
the good ones - like 'name' and 'date' - have already been used, and
will cause conflicts later.

The problem with spaces in your field names is that they make everything
slightly more difficult: you can't use
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")
and you can't use
SELECT Class Title FROM mytable
you have to use
SELECT [Class Title] FROM mytable

The display name does not have to be the same as the field name. You
can change the display name on forms, reports and queries independently
from the field name.

A good way to start learning SQL is to create your queries in the Access
"Query By Example" query design window, then switch to "SQL View"
to see the SQL that has been created.

(david)

I have two tables
1) Students: Name, Address etc.
2) Classes: Name, Class Title
The two tables are related with the Name
I need to find out if a Student has taken 4 Classes to qualify for the
5th.

For example, the Student has to have taken Algebra I, Algebra II, Calc I
and
Calc II before taking Physics.

How do I query the Classes table with the Class Title to get the names of
the Students who qualify to take Physics?

 

[david]

If you plan to switch to Oracle, I think FIELD_NAMES is still
a good idea? Last I heard, case-insensitive mixed-case field
names were still a MS extension?

(david)

Not really
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")

myval = rs![Class Title]
will work
But, I do agree with your naming conventions.

If you really want to get old school with it, table field names would be
all upper case with word seperated by underscores:
CLASS_TITLE

But Camel Case has become more popular these days

ClassTitle

Who named the fields? My rule of thumb for beginning database
designers is:
Do not use any punctuation or spaces in field names.
Do not use dictionary words as field names.

Of course, if someone else named the fields, you may have no choice,
but the problem with using dictionary words as field names is that all
the good ones - like 'name' and 'date' - have already been used, and
will cause conflicts later.

The problem with spaces in your field names is that they make everything
slightly more difficult: you can't use
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")
and you can't use
SELECT Class Title FROM mytable
you have to use
SELECT [Class Title] FROM mytable

The display name does not have to be the same as the field name. You
can change the display name on forms, reports and queries independently
from the field name.

A good way to start learning SQL is to create your queries in the Access
"Query By Example" query design window, then switch to "SQL View"
to see the SQL that has been created.

(david)

I have two tables
1) Students: Name, Address etc.
2) Classes: Name, Class Title
The two tables are related with the Name
I need to find out if a Student has taken 4 Classes to qualify for the
5th.

For example, the Student has to have taken Algebra I, Algebra II, Calc I
and
Calc II before taking Physics.

How do I query the Classes table with the Class Title to get the names
of
the Students who qualify to take Physics?

 

[gordyn5]

Allen, thanks for the reply. However I may have confused the issue. This was
only an example. Here is the real data and the real issue. The two tables and
fields are as follows
First table is called discipleList and has the following fields
Key
Last
First
Spouse
Address
The second table is called retreats ahd has the following fields
Key (same Key as the discipleList and contains multipule records with the
same Key)
Weekend
Team (where team can contain Kitchen, Chapel, Grounds and Cleanup

The discipleList contains unique records, but the retreats table contains
multiple entries for each person. For example a person could be on the
Kitchen team more than once, on the Chapel team more than once etc ...

What I am trying to do is find the people who have been on all of the teams.
If this doesn't make sense, please respond. I have 6,693 records in the
discipleList table and 16,186 records in the retreats table. I really can use
your help.

Our Lord be with you

 

[Allen Browne]

Again, building a relational structure will be imperative.

You will need tables:
Person (your first table), with a PersonID primary key
Retreat table, with RetreatID primary key (one record for each weekend)
Booking table, with a RetreatID and PersonID foreign keys.
Team table, with a TeamID primary key, a record for each task ("Kitchen",
etc.)
RetreatTeam table, with fields:
- RetreatID which weekend this person is on this team
- PersonID who is on the team
- TeamID what team they are on.

Again, your existing field names are peppered with reserved words that will
cause you problems - the first 3 for example (Key, Last, and First.)

 

[david]

Open your database in Access.
Open the query design window
Add the disciplelist and and retreats table in the design window.
Join the disciplelist and the retreats table on the key field.
Set 'kitchen' as the critieria for the tasks field.
Select the key field for display.

Save the query.
Open the query in data view.
Check that the query displays all disciples who have done kitchen duty.
Repeat for all other tasks.

Open the query design window
Add all the queries to the design.
Join them on the key field.
Display the key field
Save the query.
Open the query in data view.

(david)

 

[Klatuu]

That is the case (pardon the pun) for MS products. I am not familiar with
Oracle, so I don't know the rules for that.

If you plan to switch to Oracle, I think FIELD_NAMES is still
a good idea? Last I heard, case-insensitive mixed-case field
names were still a MS extension?

(david)

Not really
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")

myval = rs![Class Title]
will work
But, I do agree with your naming conventions.

If you really want to get old school with it, table field names would be
all upper case with word seperated by underscores:
CLASS_TITLE

But Camel Case has become more popular these days

ClassTitle

Who named the fields? My rule of thumb for beginning database
designers is:
Do not use any punctuation or spaces in field names.
Do not use dictionary words as field names.

Of course, if someone else named the fields, you may have no choice,
but the problem with using dictionary words as field names is that all
the good ones - like 'name' and 'date' - have already been used, and
will cause conflicts later.

The problem with spaces in your field names is that they make everything
slightly more difficult: you can't use
myval = rs!Class Title
you have to use
myval = rs.fields("Class Title")
and you can't use
SELECT Class Title FROM mytable
you have to use
SELECT [Class Title] FROM mytable

The display name does not have to be the same as the field name. You
can change the display name on forms, reports and queries independently
from the field name.

A good way to start learning SQL is to create your queries in the Access
"Query By Example" query design window, then switch to "SQL View"
to see the SQL that has been created.

(david)

I have two tables
1) Students: Name, Address etc.
2) Classes: Name, Class Title
The two tables are related with the Name
I need to find out if a Student has taken 4 Classes to qualify for the
5th.

For example, the Student has to have taken Algebra I, Algebra II, Calc
I and
Calc II before taking Physics.

How do I query the Classes table with the Class Title to get the names
of
the Students who qualify to take Physics?