setting criteria for multiple fields (50+)

hello all,

new to access so here it goes!

I have a table with over 50 fields on it , and a form of checkboxes with the field names that can be selected. I have a query that outputs the selected checkboxes, however I need it to return only what has been checked. Since there is about 50 fields that could possibly be checked, I need it to output the ones that are most relevant (most checked) to least relevant.

I know how to set criteria in the query and have it staggered to have it OR instead of AND, however there is only 9 rows that I could set this criteria for, and I need it for 50. Is there an easy fix to this? Or something I can code to have it account for all 50 fields ?

Any feedback would be appreciated, If I'm being too vague I can explain further.


Thanks!

You'll have to switch to SQL view. The QBE has limitations. You should probably consider redesigning the table to normalize it. You seem to have taken a spreadsheet and called it a table. You will be very unhappy using Access as a spreadsheet. Once you normalize the schema, queries will become much simpler and you won't have to worry about 50 conditions any more.

I have normalized my database as best as possible, and everything is much cleaner and working better. Im still having some trouble though.

I need to run a query based on various checkboxes on a form, and am trying to code an IF statement that will set the criteria based on what is checked . Is this the correct way to go about this? or even possible? Everytime I think I get it I don't.

I can provide screenshots if necessary.

Thanks in advance!

Start with a screen shot of your relationship diagram with the tables expanded as much as you can so we can see the column names.

vanwagnj said:

I have normalized my database as best as possible,

Click to expand...

Not trying to come down on you but we've had that statement so many times that I could retire if I had a dollar for every time it was said. We usually find plenty that needs to be revised to truly normalize it. Now, that being said, it is possible that you have it all done up correctly. But when you start to get over about 20 fields or so, the odds go up considerably that it isn't fully normalized.

I agree with Pat, let's see some screenshots of your relationships with the tables expanded so we can see all of the field names. (and a suggestion is to give us a little information as to what is being stored in which table).

Sorry for the delay! I've been out of town. I will get some screenshots and write up what the overall scope of the project is and reply asap! Thanks for the replies!

Attached are some screenshots of the form, tables, and relationships.

the company builds custom manufacturing machines, and the purpose of this is to be able to add a new record (which is already completed) and search records based on certain selections and have it output the correct jobs with those characteristics.

Basically the form is used to search for a record based on what is checked in the checkboxes. Each selection represents a characteristic on a machine, and each characteristic is listed in the table char_id . the table char_id is linked to the table tbl_customer_id which has the customer name, job # , lead ID, and some other fields that are separate to the characteristics of the machine itself.

Basically what I need is to have it run a query when I click the button that will output the jobs based on the selections made in the form, however I can't seem to get it to output the correct jobs.


I havent looked at this in a few weeks but any feedback would be appreciated! let me know if you need more info from me.

Thanks!

So I was correct in my assessment. Your relationships and table pic are quick to point out to me that they are not normalized. You have attributes which can be normalized and then you can act upon them much better. What you currently have is more like a spreadsheet format than a relational database format.

So your Char_ID table could be redesigned to be like this:

tblAttributes
AttributeID - Autonumber (PK)
AttributeDescription - Text

tblJobAttributes
JobAttributeID - Autonumber (PK)
JobID - Long Integer (FK)
AttributeID - Long Integer (FK)


And that way you can add whatever attributes are necessary for that job and there are only those that pertain to that job and not a bunch of empty fields. And it makes it easier to query upon.