Well, I think I finally found a way to do this with Access query builder. Here's the screenshot:
And the resulting SQL is:
Code:
SELECT [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].ID, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].is_inactive, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].is_subcontractor, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].company_name, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].city, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].state, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].phone_main, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].fax_main, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].email_address, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Paducah, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Hopkinsville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Owensboro, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].[KY_Bowling Green], [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Madisonville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Lexington, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Clarksville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Nashville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].IL_All
FROM [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist]
WHERE ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Paducah)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Paducah])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Hopkinsville)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Hopkinsville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Owensboro)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Owensboro])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].[KY_Bowling Green])=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Bowling Green])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Madisonville)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Madisonville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Lexington)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Lexington])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Clarksville)=Yes) AND (([Forms]![frm_company_Filter]![chk_TN_Clarksville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Nashville)=Yes) AND (([Forms]![frm_company_Filter]![chk_TN_Nashville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].IL_All)=Yes) AND (([Forms]![frm_company_Filter]![chk_IL_All])=Yes));
I'm sure this is far from efficient, but it does get the job done. For each of the 9 regions, an additional field is added to the query, based on the value of the checkbox for that region on the form. So for each particular region the region field must = Yes AND the checkbox on the form for that region must equal Yes.
I'm glad to have found a way to do this with builder, but it will be more efficient to use VBA to check the status of each of the 9 checkboxes on the form and use them to construct a WHERE clause with ORs... that only includes the regions the user is interested in. I just discovered QueryDefs and I think these will be key because its important for me to use the nested queries that are already saved in Access and also be able to save the results of this particular query as a QueryDef that can be accessed by other Access objects (without VBA).
I am interested in hearing what most experts would say is the best way to store 9+ fields such as these. Yes/No fields in the company table (actually mine are in separate table with 1-1 relationship) or in a junction table shared by company table and region table.
thanks,
baulrich