Search form with checkbox/list box and unbound keyword search

[AccessInsanity]

Hello,

I'm relatively new to Access 2010, and I'm flummoxed by something that may well be simple. It's so close in my grasp but I'm missing something. I have an excel spreadsheet that I've converted into an access table for the purposes of creating a user friendly form for the end user to search for relevant criteria to retrieve a list of records that fit the requests. There are two main search functions: unbound keyword search (90+ keywords), and either a series of checkboxes or one list box that lists specific countries/regions matched in a column (columns, as I will get to shortly). The region selection is bound by a selection of 15 countries.

The key is each record can correspond to more than one country. This, one record can have "Belgium, Germany, brazil" and the next record can just have "brazil". Thus if someone selects brazil, the query must retrieve the Belgium Germany brazil record in addition to the record that covers just brazil. I've tried to do this with a concatenated column that list all countries and a text box search, I've tried to do this with individual checkbox columns for each country with checkboxes in the search form...and I can't get it to work. I don't fully understand the criteria to set for checkboxes or list boxes. What would be the best way to build this form, an how? Any advice would be greatly appreciated?.

 

[jdraw]

The best advice I can give is -- you should make sure your table(s) match/represent your "Business issue/problem". Typically an Excel spreadsheet is NOT normalized. Getting the tables designed is key to database. And it should be done before attempting to design a form(s).
Here is a tutorial that deals with identifying Tables, and relating the Tables to meet the business requirements. The tutorial includes info on Normalization. Good luck.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[AccessInsanity]

Here is the syntax for the problem:

SELECT [Assessed Databases].ID, [Assessed Databases].Organization, [Assessed Databases].Keywords, [Assessed Databases].Global, [Assessed Databases].Japan, [Assessed Databases].South Korea, [Assessed Databases].China, [Assessed Databases].Indonesia,[Assessed Databases].Philippines, [Assessed Databases].Thailand
FROM [Assessed Databases]
WHERE ((([Assessed Databases].ID) Like "*" & [Forms]![SearchForm1].[qdbid] & "*") AND (([Assessed Databases].Organization) Like "*" & [Forms]![SearchForm1].[qorganizationname1] & "*") AND (([Assessed Databases].[Unique Description]) Like "*" & [Forms]![SearchForm1].[qdescription1] & "*") AND (([Assessed Databases].Keywords) Like "*" & [Forms]![SearchForm1].[qkeywords1] & "*") AND (([Assessed Databases].Global)=True) AND (([Assessed Databases].China)=True) AND (([Assessed Databases].Japan)=True) AND (([Assessed Databases].South Korea)=True) AND (([Assessed Databases].Indonesia)=True AND (([Assessed Databases].Philippines)=True) AND (([Assessed Databases].Thailand)=True);

The goal of this query is to aid the end user to type in specific keywords (such as Agriculture) in the keywords text box (ie, (([Assessed Databases].Keywords) Like "*" & [Forms]![SearchForm1].[qkeywords1] & "*")) with qkeywords1 the name of the textbox in the form, and select one or more countries from the country checkboxes (China, Japan, South Korea, etc). The form (SearchForm1) has a run query button (query titled mainquery) that, in theory, should pull up all the records that match the criteria of "Agriculture" typed in the keyword box and the Japan checkbox selected, or the Japan and Indonesia checkboxes selected. Each country has a column dedicated to it in the table, with values converted to -1 and 0 to affirm if the record applies to the country, and in the table the country columns are represented by checkboxes. These columns are all checked in the table to be Yes/No, Lookup Checkboxes.

I can see problems with the SQL syntax, I just don't know how to overcome them. I don't know how to connect the query to the Form as far as checkbox language. I can understand how to do it with an unbounded text box, but a check box, I don't know how to make that connection. I don't know how to allow records to be retrieved when boxes are unchecked, for example, when the user selects Japan but doesn't select Thailand, I want the user to still be able to retrieve records that are checked for Japan and Thailand, as opposed to Japan and NOT Thailand (ie, unselected textbox does not mean exclusion of the value from the query). These are the dilemmas, and my books aren't helping me and all the google searches for (access criteria checkbox) or (SQL checkbox syntax access) are yielding little that my novice mind can comprehend.

Kind regards