Design view input????

[Guest]

Hi,

I'm going to give a simple example of my DB. I have two tables:
Student: (2 fields) student_id (atuo number), name (string)
Event: (2 fields) student_id (key to student table), event (string)

There is a one (student) to many (event) relationship.

I have a form with a command button, a check box, and an 'event' combo box.
I want the command button to pull up a report based on what's in the combo
box or the check box. If the check box is checked the combo box is blank.
If an event is selected in the combo box the check box is unchecked. If
there is an entry in the combo box I want the report to show only the
students in the selected event. If the check box is checked I want the
report to show the student in any event.

I called the combo box 'cboevent', and the check box 'ckall'

How do I enter the selection criteria in the design view of the query table?
Is it possible to do it there or do I have to do in SQL view? I can't
figure it out..... I would like to do it in design view if possible.

Thanks,

 

[Jeff Boyce]

Phil

I don't know what the details are of your situation, but, on the surface, it
strikes me as perhaps unusual that you'd have "custom" events for each
student. At least, that is what your data structure description implies.

I'm more familiar with a situation in which one student may participate in
many events, but each event could have many students -- and this is a
many-to-many relationship. The m-to-m relationship needs three tables:
tblStudent, tblEvent, and a third, resolver/junction/relation table,
trelStudentEvent. That third table would hold, at a minimum, the StudentID
from tblStudent and the EventID from tblEvent. This third table holds a row
for each student-event combination that is valid.

 

[Guest]

In situations like this, I would let the query select everything then apply
the required filter when the report is opened by utilising the Where
condition. In your example it would look something like

If ckall then
DoCmd.OpenReport "YourReport"
Else
DoCmd.OpenReport "YourReport",,,"event = '" & cboevent.Value & "'"
End If

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

Here is a simple SQL statement to do what you want. Be warned that Access will
rearrange this when you save the query. So when you look at it later it will
seem more complex than what you originally entered

SELECT *
FROM Student INNER JOIN Event
ON Student.Student_ID = Event.StudentID
WHERE Event.Event = Forms!FormName!CboEvent
Or Forms!FormName!ChkAll = True

If you are using the query grid to build this, then under Event.Event you would
enter the following criteria in ONE criteria Cell on one line
Event.Event = Forms!FormName!CboEvent Or Forms!FormName!ChkAll = True