need help with query criteria !

[vanwagnj]

Hello All,

I'm fairly new to access and am in a bit of a bind. I have a query that outputs data that is selected from a form full of checkboxes, and there is about 50 fields. I am trying to set the criteria for it so that it only shows the records when the checkbox is checked. I know entering true on staggered lines for the criteria will solve that problem, however I have about 50 fields that I need to account for, and there is only 9 rows that I can go down to with criteria keeping it an OR instead of an AND.

Is there an easy way to code this to get it to set the criteria for all of the fields?
Or should I split up my main table into different tables?

any feedback would be appreciated. If im being too vague just let me know and I can explain further or attach a screenshot.



Thanks!!

[orange]

should I split up my main table into different tables

If you have everything in 1 table, you really should read this
http://forums.aspfree.com/attachment...achmentid=4712

[vanwagnj]

Thank you sir! I will read through this and see what I can do!

[vanwagnj]

I have normalized my database and everything is much cleaner and easier to work with, however I still have a table with multiple fields that are characteristics for custom made assembly machines, and I have a form with these options that I want to be able to run a query by what is checked in the checkboxes .

I am currently trying to write a big if statement accounting for each option and saying that if it is true on the form to set the value to true for the query.
Is this the right way to go about this?
If so I seem to be having some trouble with my If statement, but if there is a better way I am all ears!

Thanks in advance.

[orange]

Please show us your table and relationship structures?
Also can you get a screen shot of the form you're talking about?

You could run a query (or embedded query in vba) that gets the values of checkboxes from the Form controls.

Show us sql or vba for whatever you have.

Before you get too much involved in the syntax of a long, complex IF statement, I suggest you make a small logic diagram
to make sure you fully understand, the checkboxes, what you want to happen if a checkbox is checked, and the flow from one checkbox to another. Once you are certain of the logic you could possibly program/code the set up in various ways.

[vanwagnj]

I've attached what I can!
I'm afraid my code is useless at the moment. Hope this Helps, I'll look into making an embedded query based

[orange]

I think you have work to do on your data model/table structures.

Your tables show a 1:1 relationship -- which I strongly doubt, but don't know your application.

Before getting too deeply involved in coding and queries etc. get a meaningful data model.

Do this as first step: Write a paragraph or 2 of exactly what the application/business is. What are the major processes? What are the things involved? These are known as "Business Facts".

see http://www.databaseanswers.org/approach2db_design.htm and
http://www.rogersaccesslibrary.com/T...lationship.zip

Here's a model and web site that may give you ideas.
http://www.databaseanswers.org/data_...tics/index.htm and the related facts
http://www.databaseanswers.org/data_...tics/facts.htm

[vanwagnj]

Sorry for the delay! I have been out of town a few weeks. I will write something up and reply asap.

I think you have work to do on your data model/table structures.

Your tables show a 1:1 relationship -- which I strongly doubt, but don't know your application.

Before getting too deeply involved in coding and queries etc. get a meaningful data model.

Do this as first step: Write a paragraph or 2 of exactly what the application/business is. What are the major processes? What are the things involved? These are known as "Business Facts".

see http://www.databaseanswers.org/approach2db_design.htm and
http://www.rogersaccesslibrary.com/T...lationship.zip

Here's a model and web site that may give you ideas.
http://www.databaseanswers.org/data_...tics/index.htm and the related facts
http://www.databaseanswers.org/data_...tics/facts.htm

[vanwagnj]

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!

[orange]

If the table char_id has several attributes that relate to a job, then it is definitely Not 1:1

eg, 1 job has many attributes, so 1 job in the customerTable has many records (1 for each attribute) in the Char_id table.

You may want to build a lookup table for Attributes such as

attributeId 01
attributeName Orbital

If you come up with more attributes at some time, you can just add to the table.

Then you Char_id table would have records of

JobNumber 206    206  206
AttributeId  01     03    19

Note: Field and object names should NOT contain embedded spaces nor special characters.


Do have names for these machines, or models --- something more than a series of terms like Orbital , MIG ?