Filtering check box properties by multiple combo boxes on a form (1 Viewer)

[leadhilldog]

Please can someone help me. I am trying to have some sort of reference database for photographs. I have a number of fields on a form which are check boxes with field names to describe each photograph. E.g. landscape, sea, close-up, animal. Each photograph having between one and 10 boxes checked.

On the form I want to have combo boxes listing all of the check box properties names. I want to select properties of each photo in the combo boxes and press a button to open a form to show the filtered photos which have all of the selected properties.

Note: if I only select a property in one combo box I would like the filter to show all records with that property and disregard the fact that the other combo boxes are blank.

Go easy on me - I'm pretty new to all this stuff.

 

[Abby N]

Rather than use a bunch of combo boxes on your search form, why not use checkboxes there as well? I think it will be more intuitive for your users and simpler to reference in your query.

~Abby

[This message has been edited by Abby N (edited 08-23-2001).]

 

[leadhilldog]

Ok so let's say I do that. How can I filter the records by the check boxes they have selected but also include the records with these and other boxes ticked also?

Thanks in advance.

 

[KevinM]

NO, your design is wrong.
You need TWO tables, one to store your photographs and a related table to store the descriptions.

As each photograph can have MANY decriptions, then you need to create a one to many relationship between the two.

Also, create a lookup table that stores all the possible descriptions.
Your many table simply selects from this look up table via a continuous subform.

Believe me, this is the best (and only) way of doing it.

It will be MUCH easier to query/filter for a particular description(s), as you only use ONE column rather than several check boxes.

HTH



[This message has been edited by KevinM (edited 08-24-2001).]

 

[leadhilldog]

Thanks for that Kevin. I'll try it that way.

 

[Abby N]

Kevin, well that was assertive. *g* You are absolutely correct that the multiple table method is the preferred way of handling this situation. Though multiple Boolean fields in a single table will work (albeit clumsily) and are much simpler to conceptualize for a beginner.

Leadhilldog, what you are now dealing with in your table structure is a many-to-many relationship. One photo can have multiple descriptors and each descriptor can be applied to multiple photos. These can be a little confusing at first and are probably best handled with three tables. One listing each photo, one listing each descriptor (What Kevin called a lookup table) and a third table linked to the primary key of the other two that will track which photos have which descriptors. If you'd like a sample DB to help you out, let me know what version of Access you're using and I'll direct you to one online or email it. You may also want to lookup "Many-To-Many Relationships" in the help file.

Once you have your tables set up the query may work itself out. But, if you still have any problems you know where to find us. Good luck!

~Abby

P.S.
I still recommend checkboxes on for your search form. My reasons are two fold. First, because checkboxes are the way the users are entering the descriptors, checkboxes on the search form provides a sense of consistency. Second, they allow the user to select multiple criteria. Another option for specifying multiple criteria would be a multiselect listbox. But, those are a whole new can of worms.

[This message has been edited by Abby N (edited 08-24-2001).]

 

[KevinM]

Abby,

Everyone has to start somewhere and I always encourage beginners to use related tables and 'normalisation'. Doing it 'the wrong way' only leads to frustration and extar for the beginner. I have seen this time and time again.

The multiple check box method you mention is severely flawed (even if it is more 'user friendly'). It is bad db design and should not be enccouraged on this forum or anywhere else!
After all, we are talking about a database here and not a web page.

What if new descriptions are required?
Then the user will not be able to add these, it will be the developer who will have to make several design changes to the table, forms, queries, reports, vb code etc everytime a new description is required.

How do you filter, sort, search etc on a description. You will have to go through multiple fields everytime where as with 'my' method you only need to work with one and never need to make any design changes.

You could indeed use check boxes alongside each Description in a continuous form and then run an append query to append all those ticked. I have used this method a few times, although it just doing the same as selecting the values from a combo box.

Even if the descriptions would always be the same and never require additions then I would still do it this way.

 

[Abby N]

Kevin,

I agree wholeheartedly that the many-to-many relationship table structure is better. I wish I’d thought of it first. I was so intent on the form and query I never gave the table structure a second thought. I must be getting addlebrained in my old age.

Going back and rereading my response I see it came out with an adversarial tone I had not intended. I apologize for that. I did not mean to marginalize or dispute your suggestion, but expound on it. My objective was to inform Leadhilldog about the basic outline of what he is now undertaking and let him know what the term for it is.

~Abby

 

[leadhilldog]

Thanks for all this Abby and Kevin.

Abby, please could you send me a sample database to get an idea of what I should be doing. I'm using Aceess 97.

Thanks again for all your help.

 

[Abby N]

Just sent it off. If you have any questions feel free to email me.

~Abby