if or case statement and how

[Dannat]

if I have one table that has all the names of the cities where an employee can work. The cities are column names. I need to be able to search on these columns depending on what the user inputs. Example:

The employee says they can work in Ottawa, Toronto and Barry. In the table under those city columns it will have the falue of True in the fileds and all the other will be False.



Now a new jobs comes in, it goes into a different table and the city column says Ottawa. How do I create a querry to take the "Ottawa" from the second table and find the "Ottawa" Column in the 1st table and see if it is true. If it is then I need the employee name to show. If its false then do nothing.

[jzwp11]

Your table structure is not set up correctly. If a person can work in many cities that describes a one-to-many relationship. Additionally, more than one person can probably work in the same city, so you have another one-to-many relationship. The proper structure is as follows

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblCity
-pkCityID primary key, autonumber
-txtCity

tblCityPeople
-pkCityPeopleID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkCityID foreign key to tblCity