Please help filtering records based on multiple checkboxes

[ajk825]

Hi, please someone this is the last piece of this project I need to complete...I will try to explain this as best I can. I am using Access 2013, I have a recipe project with multiple one-to-many relationships. The main table in all of them is RECIPE. Child tables are HOLIDAY, SPEC_NEED, COURSE... each one of these child tables are comprised of multiple checkbox columns (yes or no)... I need to filter the RECIPE records based on the selected checkboxes in these child tables..so for example if COURSE.Appetizer is checked and HOLIDAY.Christmas is checked RECIPE will produce the appropriate records. I have no idea how to pull this one off...I am currently using the Options Group design tool and have used a separate Options Group design for each of the child tables... This is the last I need to do to finish this project...Any and all help would be extremely appreciated.
Thanks in advance,
Adam

[June7]

Have you looked at the MS Recipe db template? http://office.microsoft.com/en-us/te...001018635.aspx

I suspect your db structure is not normalized. Anyway, what you describe doesn't make sense to me. If you want to provide db for analysis, follow instructions at bottom of my post.

[ajk825]

My database is normalized.....Even with all the info removed and some table that are not relevant it is still 9.03Mb zipped....so how else can I get it to you?

[June7]

Did you run Compact & Repair then zip the file? 2MB zip allowed. Still too large? Can upload to fileshare site such as Box.com and post link to file.

[ajk825]

Hi June7...thnx for the help I really appreciate it..I did what you asked...here's the path : http://app.box.com/s/zczp3zywngshsdpoph6k
I left it in it's original state since there was no size issue with it as it is...The Form I'm having my problems with is the RECIPE_SEARCH_FORM... I deleted the button and forgot to replace it...there should be a button on the bottom right of the form that when pushed filters the listbox by all the check boxes on the form... and the various text boxes(fat, calories, carbs etc...) by > whatever is entered into them...I know this sounds confusing and I apologize but it's the best I can explain...hopefully it makes more sense when you look at it....Thnx again for everything.

[June7]

Your tables are not truly normalized. Multiple yes/no fields for same data element is an indication of non-normalization. For example, Holiday table normalized would be

tblRecipeHolidays
RecipeID
HolidayID (PK from Holidays table or just the actual text holiday name)

This would mean multiple records in tblRecipeHolidays for each recipe. With your current structure if a new holiday is added would have to modify table, queries, forms, reports instead of just adding a new record to Holidays.

With current arrangement might as well just be one big flat file table, which is essentially the result of the query for the search form. Queries have same field limit as tables, 255.

The query is set with INNER JOINs. INNER JOIN requires related records in all tables. Change to LEFT JOINs - "Include all records from Recipe ...". This is an advantage of the big flat file, no issue with join type.

I have tried helping other posters with dynamic filtering using multiple checkboxes and the efforts are always frustrating. Yours is the most demanding I've seen. Will need code behind each option group. Here is an example of conditional code to build filter string: http://allenbrowne.com/ser-62code.html. Your construct will use only the OR operator. I think there is a limit on number of AND/OR operators in a criteria clause, maybe 99.

[ajk825]

well this sucks... and I had my professor look my database over before i started this....what do you suggest?

[ajk825]

Sorry...just re-read your last post...You miss understood something here....the user would not be entering new Holidays...the holidays that are in the table are the ones they can use...that's it, same with Course, Spec_Needs, etc...these are choices....they can input new Recipe's and those recipes can choose the holiday they are for...(if that particular holiday is listed...I obviously don't have every holiday there are)... not enter new Holidays or Course themselves. So say a user enters a new recipe they can hit the AddCourse tab and if it is an Appetizer they check Appetizer, if it is an Entree, they check Entree...if not, they leave them alone...if no Course info is entered a new Course record is not added to COURSE....

[June7]

I did not misunderstand. I did not mean the user would be entering holidays. I meant if it became necessary to add another holiday option this would mean the developer (you) would have to modify table, queries, forms, reports. Same for the other data elements (Course, Spec_Needs, etc). If these changes will be rare and you can live with having to make the mods if the requirement ever did come up, that's your choice. The point is this is not a normalized design.

Correction to my statement about the OR operator, probably want AND. All other comments/suggestions still apply.

I just noticed something in that query I've never seen before. Some of the checkboxes are solid, neither empty nor check mark. Never seen that in table or query, only on forms.

Also, once a checkbox is checked within a group, that group cannot be deselected, one of the items must remain selected. I find that annoying.

Did you look at the MS database?

[ajk825]

Yeah I looked at it...well thnx for the help.