how to dispay full course offering while marking what student took

[Priscilla]

What is the best way to accomplish the following? Say I have three tables:
Students (list all students), ClassesOffered (list all classes offered) and
StudentandClasses (list classes student has taken).
Is there a way with in a form/query to display a list of all classes
available along with some type of distinction indicating what classes the
student already took? The student would then select new classes to take for
the next semester. I cannot seem to display things in this manor.

Table: ClassesOffered
dblClassID chrName
1 English 101
2 American Lit
3 Math 101
4 Advance Algebra

Table: Students
dblStudentId chrName
1 Paul
2 Peter
3 Chris

Table: Student and Classes
dblClassID dblStudentId
1 1
1 2
2 1

Anticipate display
Student: Paul
Classes: Class Taken
English 101 Yes (somehow mark this as a class taken as read-only or
different color
American Lit Yes (somehow mark this as a class taken as read-only or
different color
Math 101
Advance Algebra

Thanks in Advance

 

[strive4peace]

hi Priscilla,

I am going to use generic fieldnames for this example -- substitute your
actual fieldnames

make a query that shows all combinations of Classes and Students. This
is called a Cartesian query:

query name: qAllClassesStudents
SELECT ClassID, Classname, StudentID, StudentName
FROM Classes, Students

make a new query with the query designer

~~~
link qAllClassesStudents to [Student and Classes] on both the StudentID
and the ClassID

edit both the Join lines by double-clicking:

show all records from qAllClassesStudents and just those from [Student
and Classes] where the joined fields are equal
~~~

link your Classes table and Students table to qAllClassesStudents on the
appropriate ID

choose fields to show and, on the grid, also do this:

field --> ClassTaken: iif(isNull([Student and
Classes].[Fieldname]),"No","Done")

~~~
btw:
Single and Double precision numbers are stored in floating point format
(exponential format: 1.234 x 10^^4) and are susceptable to creating
"Ghost" digits several places out...so they are not accurate for exact
comparisons (even if the data "appears" to have no decimals) -- so do
not use that data type for key fields; use Long Integer instead

maybe you imported data and did not edit data types?


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*