Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search multiple values in multiple tables

Status
Not open for further replies.

andyhall

Technical User
Nov 20, 2002
5
US
I have four tables (Contacts, LanguagesExcellent, LanguagesGood, and LanguagesFair) with a key of ContactID. I'm trying to write the SQL that will allow users to enter no, one, or several options for each of the three language proficiencies (i.e., Excellent, Good, and Fair) and return the contacts for the matching criteria. I've got it to work if I enter nothing or just one value but I'm stumped beyond that. Specifically, if I enter more than one value, the query only looks at the last value in the list. How can I get it to grab all of the values? Secondly, once it does return multiple values, what is the code for iterating through each instance...possibly for one, two, or all three of the language proficiences? The code I have so far is below. Also, I have the users querying from an "input form" and, specifically, using three subforms for entering the three different language proficiencies.

Thanks for any help!!!

SELECT tblContacts.ContactID, tblContacts.FirstName, tblContacts.LastName, tblContacts.PositionTitle, tblLanguagesExcellent.LanguageExcellent, tblLanguagesGood.LanguageGood, tblLanguagesFair.LanguageFair
FROM ((tblContacts INNER JOIN tblLanguagesExcellent ON tblContacts.ContactID = tblLanguagesExcellent.ContactID) INNER JOIN tblLanguagesFair ON tblContacts.ContactID = tblLanguagesFair.ContactID) INNER JOIN tblLanguagesGood ON tblContacts.ContactID = tblLanguagesGood.ContactID
WHERE (((([tblContacts].[GenderExperience])=[Forms]![frmQueryByForm]![GenderExperience] Or [Forms]![frmQueryByForm]![GenderExperience] Is Null)<>False) AND ((([tblLanguagesExcellent].[LanguageExcellent])=[Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Or [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Is Null)<>False) AND ((([tblLanguagesGood].[LanguageGood])=[Forms]![frmQueryByForm]![sfrLGQry]![LanguageGood] Or [Forms]![frmQueryByForm]![sfrLGQry]![LanguageGood] Is Null)<>False) AND ((([tblLanguagesFair].[LanguageFair])=[Forms]![frmQueryByForm]![sfrLFQry]![LanguageFair] Or [Forms]![frmQueryByForm]![sfrLFQry]![LanguageFair] Is Null)<>False));
 
I don't know what datatype LanguageExcellent is, but if it's text you could change

((([tblLanguagesExcellent].[LanguageExcellent])=[Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Or [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Is Null)<>False

to

[tblLanguagesExcellent].[LanguageExcellent] Like [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent]

When the field on the form is filled in, you'll have to match on the contents, but when the field is empty you'll match on all records that DON'T have a match.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top