Either Or criteria/condition

[ipr]

My database is meant for faculty members at my university to scout out potential research partners with similar interests.
I have a table with faculty members' names, and then a list of about 54 keywords which might be applicable to that faculty member's research. Each keyword is its own field, and all of the keywords are Yes/No values.

An example [for arguments sake, using dummy keywords] runs like this, with the field names first:
Last: Miller
First: Larry
Blue: Yes
Red:
Green: Yes
Yellow:

Then the next entry, using those same fields, would be:
Last: Muldrich
First: Maria
Blue:
Red: Yes
Green: Yes
Yellow: Yes

Suppose a faculty member wants the research interests "Blue" and "Red."
With the code I have now, neither Larry Miller nor Maria Muldrich would turn out, because neither one has BOTH Blue AND Red.

Instead, I would like the query to return both Larry AND Maria, because they fit the criteria of having EITHER "Blue" OR "Red."

I have tried replacing my ANDs with ORs in my query's SQL [Originally was using an IIF statement]. The query does not run at all and instead gives me my entire table.

My boss is planning on demonstrating my database to his supervisor VERY soon, so a quick answer would be welcome, if at all possible. Thanks!

 

[Alansidman]

I believe you would have greater success if you had a normalized tables. One table with Faculty information. A second table with a faculty ID to link to the faculty table and then a field with characteristics. You would have a one to many relationship between the tables. One faculty member has many characteristics. You would then query on the characteristic field the criteria you are looking for and it would return all faculty members with those characteristics. Here is a good read on building a relational database.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[spikepl]

I second Alan. What you have is a spreadsheet not a relational db. This has highly impractical consequences - you have stuffed similar info into different containers, so to find some info you need to know in which container (=column) to look. This runs contrary to data normalization and means that all queries, reports and operations on the data are a complete pain in the derrière.

 

[ipr]

By "characteristic" do you mean "keyword"?

When I have my query results, only the faculty ID number should be displayed next to the list of keywords, not their name? And then the query would run JUST on the keywords? Is this what you are telling me?
I do have a different, master table with all of the faculty members' names and ID numbers, along with other information.

 

[BigHappyDaddy]

Example:

tblFaculty
FacultlyID
FirstName
LastName

tblResearchInterests
InterestID
InterestName

tblFacultyInterests
FacultyID
InterestID
​

The tblResearchInterests would hold the 54 keywords. Blue, Red, Green Yellow, etc

The tblFacultyInterests would hold the facultlyID and the InterestID. It hold the relationship between the faculty member and his/her research interests.

Then finding facultly members with similar interests becomes almost a trivial query.

Normalized data makes writing this type of query very easy, rather than the 'experience' your have faced.

 

[ipr]

tblFacultyInterests
FacultyID
InterestID

But wouldn't that require me to put multiple values in one value field, i.e.
tblFacultyInterests
FacultyID [1]
InterestID [1, 3]
to represent
Larry Miller, Blue, Green
??

 

[Alansidman]

No, you list each value as a record
FacultyID 1
FacultyInterest 1
FacultyID 1
FacultyInterest 3

etc. Avoid putting multiple items into one field. You will only experience more pains in your backside.

Did you read the white paper on data normalization?

 

[ipr]

Got it working. Thanks guys!