multiple category (yes/no) query

[SimonBH]

I have a table with company names and then 10 yes/no boxes that represent what type of company they are (graduate, non-graduate, misc, co-oop, etc...) Every company has at least one box "checked" yes, some have 2 or more.
The problem that i am running into is that I am trying to design a form that will allow a user to select one or many of these categoryies and output the results to a report. It works for companies that only have one box checked, but not for more than one. Any help would be most appreciated. Sample code below:

SELECT DISTINCT Company.Organization, Contacts.[First Name], Contacts.[Last Name], Contacts.Position1, Contacts.Phone, Company.Address1, Company.Address2, Company.City, Company.State, Company.ZipCode, Contacts.[Email Address]
FROM Company INNER JOIN Contacts ON Company.Organization = Contacts.Organization
WHERE (
(Contacts.[Phone]) is not null AND
(
(
((Company.[Health])=[Forms]![Form Master]![HHG]) OR
((Company.[Health Grad])=[Forms]![Form Master]![HHG])
) AND
(
((Company.[Other])=[Forms]![Form Master]![OOG]) OR
((Company.[Other Grad])=[Forms]![Form Master]![OOG])
) AND

((Company.[Edu])=[Forms]![Form Master]![EDU]) AND
((Company.[Co-op])=[Forms]![Form Master]![COOP]) AND
((Company.[Outreach - Other])=[Forms]![Form Master]![OutOther]) AND
((Company.[Outreach - Health])=[Forms]![Form Master]![OutHealth]) AND
((Company.[Outreach - Grad])=[Forms]![Form Master]![OutGrad])
)
)
ORDER BY Company.Organization;

 

[Dustman]

Try using XOR instead of OR.

XOR is the operator for "and/or". -Dustin
Rom 8:28

 

[SimonBH]

ok, i solved it on my own. Here is what I did. After the user selects the check boxes, the then press a button, bound to that button is this vb script:

Public Function ProcessType1()
DoCmd.SetWarnings False
If ChkTdfExist(sTName:="temp&quot = True Then
' ChkTdfExist function taken from another thread
DoCmd.DeleteObject acTable, "temp"
Else
End If
DoCmd.CopyObject , "temp", acTable, "Contacts"
DoCmd.RunSQL ("Delete * FROM temp&quot
If [Form_Form Master].HHG = True Then
DoCmd.OpenQuery "sub - HHG"
End If
If [Form_Form Master].OOG = True Then
DoCmd.OpenQuery "sub - OOG"
End If
If [Form_Form Master].Edu = True Then
DoCmd.OpenQuery "sub - EDU"
End If
If [Form_Form Master].COOP = True Then
DoCmd.OpenQuery "sub - COOP"
End If
DoCmd.OpenQuery "sub - cleanTemp"
DoCmd.SetWarnings True
End Function


The above script checks to see is a table called temp exists , if it does, it deletes it. It then copies the contacts table to temp table. It then clears out all records from temp. Next it populates the temp table using the queries for each checkbox. The last query "sub - cleanTemp" returns all of the distinct records in temp.

the subQueries are like this:

INSERT INTO temp
SELECT Contacts.[Last Name] AS [Last Name], Contacts.[First Name] AS [First Name], Contacts.Organization AS Organization, Contacts.Position1 AS Position1, Contacts.Address1 AS Address1, Contacts.Address2 AS Address2, Contacts.City AS City, Contacts.State AS State, Contacts.[Alt Phone] AS [Alt Phone], Contacts.[Zip Code] AS [Zip Code], Contacts.Phone AS Phone, Contacts.[Fax Number] AS [Fax Number], Contacts.[Email Address] AS [Email Address]
FROM Company INNER JOIN Contacts ON Company.Organization = Contacts.Organization
WHERE ( ((Company.[Co-op])=Yes) )
ORDER BY Contacts.[Organization];

And lastly, the query "sub - cleanTemp" looks like this:

SELECT DISTINCT *
FROM temp;


Hope that someone can use this.