How do I do this?

R

r0adh0g

I database with three tables:

tblStudents w/fields SID, Last, First

tblClass w/fields CID, CName, CDescription

tblRegister w/fields RID, SID, CID

I want to create a report for each student. This report will list all
classes offered.
Beside each class offered will be a check box. It the studend is registered
for said
class, the box will be checked. Sounds simple but I've beat my head against
the wall
for two days on this one. Any help? I am totally open for suggestions.

Thanks,

rh
 
A

Allen Browne

1. Create a query containing tblStudents and tblClass.
There should be no line joining the 2 tables, so you get every possible
combination.

2. Drag the fields you want into the grid.

3. In a fresh column in the Field row, enter:
IsRegistered: EXISTS (SELECT RID FROM tblRegister
WHERE ((tblRegister.SID = tblStudents.SID)
AND (tblClass.CID = tblRegister.CID)))

The subquery returns True (-1) if the combination of student and class is
found in tblRegister, else False (0).
 
R

r0adh0g

Thank you Mr. Browne, this gets me the data I need.
It appears that you cannot group the data by the students name.
I get an error "Multi-level GROUP BY clause is not allowed in a subquery"
Any way to get around this, or will I have to live with no grouping?

Thanks for any and all of you help in advance!

rh
 
R

r0adh0g

Thank you!

rh

Peter R. Fletcher said:
My initial reaction was to think: "Silly fellow, what's the problem?"
- then I looked at it more carefully!

I assume that tblRegister has one-to-many links with tblStudents (on
SID) and tblClass (on CID). The problem is generating a Query to act
as source for the Report, and, as far as I can see, you can't, or at
least not without writing some VBA Code. I came up with the following
solution:

1) Create a Query called qryClassStudents, whose SQL is

SELECT tblClass.CID, tblStudents.SID FROM tblStudents INNER JOIN
(tblClass INNER JOIN tblRegister ON tblClass.CID = tblRegister.CID) ON
tblStudents.SID = tblRegister.SID;

2) In a General Module, place the following Function -

Function IsInClass(StudentID As Long, ClassID As Long) As Boolean
Dim rstStClass As New ADODB.Recordset
With rstStClass
.Open "SELECT * from qryClassStudents WHERE SID = " & StudentID,
CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If .EOF And .BOF Then
IsInClass = False
Else
.MoveFirst
.Find "CID = " & ClassID
If .EOF Then
IsInClass = False
Else
IsInClass = True
End If
End If
.Close
End With
Set rstStClass = Nothing
End Function

3)Create a query called qryRegistration (or anything else you like!),
whose SQL is

SELECT tblClass.CID, tblClass.ClassName, tblStudents.SID,
tblStudents.FirstName, tblStudents.LastName, IsInClass([SID],[CID]) AS
Registered FROM tblClass, tblStudents;

qryRegistration will contain one record for each _possible_
combination of class and student, and is a rare example of what is
known as a Cartesian Product of two tables actually being useful! You
can easily base the Report you want on qryRegistration


I database with three tables:

tblStudents w/fields SID, Last, First

tblClass w/fields CID, CName, CDescription

tblRegister w/fields RID, SID, CID

I want to create a report for each student. This report will list all
classes offered.
Beside each class offered will be a check box. It the studend is registered
for said
class, the box will be checked. Sounds simple but I've beat my head against
the wall
for two days on this one. Any help? I am totally open for suggestions.

Thanks,

rh
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
 
R

r0adh0g

Thank you for both of your help. Here is how I solved my problem.
Created two queries:

qryStudentClass1:

SELECT tblStudents.SID AS StudentID, [Last] & ", " & [First] AS [Student
Name], tblClass.CID, tblClass.Cname, tblClass.CDescription
FROM tblClass, tblStudents
ORDER BY tblStudents.SID, tblClass.Cname;

qryStudentClass2:
SELECT qryStudentClass1.StudentID, qryStudentClass1.[Student Name],
qryStudentClass1.CID, qryStudentClass1.Cname, qryStudentClass1.CDescription,
IIf(IsNull([RID]),False,True) AS [Enrolled?]
FROM qryStudentClass1 LEFT JOIN tblRegister ON (qryStudentClass1.CID =
tblRegister.CID) AND (qryStudentClass1.StudentID = tblRegister.SID);

Based report on second query:

What a pain for what at first looks like a simple task.

Thanks again.....

rh
 
Top