NEED HELP ASAP on using queries as a record source for forms

[desmonda]

For my assignment at school, I had to design a sport information system. One of my objectives was to create an information system where we can type in a student and their gender, and then the sport drop down list is filtered to only display sports where the genders are the same or the gender is unisex. I'm stuck on how to implement this. It keeps replicating the data. In my form, I want a person to enter in their gender and the next drop down combo box to filter the sports they can choose based upon this. Any help would be greatly appreciated. I've wasted soo much time on this now and it is due next week

This is the current query I have.

Select S.Sport
From SportDetails AS S, StudentDetails AS T
Where S.Gender = T.Gender OR S.Gender = "unisex'

My teacher has created this code but it still does not work

SELECT d.sport
FROM studentdetails AS s, sportdetails AS d
WHERE (s.gender = d.gender or s.gender = "unisex")


and s.eqid like ["please enter an id"] & "*";

[ranman256]

I don't see how the teachers will work.
gender is on the student table. Sport.gender is the limiter to match.
so you must have both table but not join them.
the unisex will throw it off.
i would use 2 different queries; 1 for unisex,1 for gender specific.

[orange]

Any help would be greatly appreciated.

I suggest you write a description of WHAT you are trying to do. You said in overview I had to design a sport information system. Then, you jumped into HOW you were doing certain things that are not working as expected.
You are showing 2 tables.

I don't know the details of your assignment or what your expected outputs/queries might be.
Consider the following --and I could be way off-base with my first reaction:

There are Many Students.
Students are Categorized by Gender.
Gender is restricted to Male, Female, Unisex
There are Many Sports.
Each Sport is categorized by Gender (same restriction as Student gender)
A Student may participate in 1 or many Sports.

tblStudent
StudentID PK
StudentFirstName
StudentLastName
StudentGender
other student info

tblSport
SportID PK
SportName
SportGender
other sport info

You then say In my form, I want a person to enter in their gender and the next drop down combo box to filter the sports they can choose based upon this.


So you form should have a control where the user can enter their gender. eg txtGender

Your combobox -to show appropriately Sport names -- would need a rowsource that is filtered.

The rowsource would be the tblSport and the filter/criteria would be the value entered into txtGender.
However, for Gender ---unisex applies to all. So you must account for it in your filter/criteria.
eg Where SportGender = Gender entered by user OR SportGender = unisex

In order to show the filtered Sports in the combobox, you would need to Requery your form.

I hope this is helpful.

[desmonda]

Hey, thanks for your help, how did you manage to filter the data?

btw There are some sports eg. rugby that are not offered for girls. Each sport has a girl/boy variant and some have only one. The unisex ones are like debating

[orange]

I mocked up your situation:

tblStudent

studentID	Studentname	StudentGender
1	Bob	male
2	Sam	male
3	Jo	female
4	Joe	male
5	Josh	male
6	Tara	female
7	Sara	female

tblSport

sportID	sportName	sportGender
1	BasketballF	female
2	GymnasticsF	female
3	GymnasticsM	male
4	GymnasticsU	unisex
5	BasketballM	male
6	SwimmingU	unisex
7	CricketU	unisex
8	CricketM	male

On the form there is a textbox txtGender and a combo cboSport

The AfterUpdate event of the txtGender is

Option Compare Database
Option Explicit

Private Sub txtGender_AfterUpdate()
  Me.cboSport.RowSource = "Select * from tblSport where sportGender = 'unisex' or sportGender ='" _
                        & Me.txtGender & "'"
  Me.Requery
End Sub