Criteria for multiple fields

H

Hutch

I have a table that contains 30 check boxes in it. This table is only used
when someone removes a vehicle for work. The boxes are used to note if there
is a problem with the vehicle, i.e. Throttle does not work (Check Box).

I need to create a query that would look for any field that has a box
checked and show it. Is this possible with so many fields. I know the
criteria field is limited to 10, is there another way I am missing?
 
D

Douglas J. Steele

You should really reconsider your design. What you've got are usually
referred to as "repeating fields", and one of the biggest problems is that
the details of what the field means is stored in the field's name.

You should insert a row into a second table corresponding to each problem.
In other words, rather than having 10 out of the 30 checkboxes checked for a
particular vehicle, you'd have 10 rows in a second table pointing to that
vehicle.

One of the fields in the second table would be a description of what the
problem is.
 
D

Douglas J. Steele

Rather than having a row that stores the information about what vehicle it
is, when it came in and so on, pluse check boxes for "Throttle does not
work" and one for " Brakes are squealing" and a third for "Ashtray is full"
on the one row, you'd only store the information about the vehicle and visit
in the one table. You'd have a second table that would contain 3 rows. All
three would contain a field with the primary key value of the previous
table. Each of the 3 rows would contain the details of what was wrong.

For more details about database normalization, check out some of the
references Jeff Conrad has listed at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
 
J

John Spencer (MVP)

AND if you are STUCK with this design and I do mean stuck. You can add more
rows to the criteria section of the query grid.

--Click in a criteria row
--Select Insert: Rows from the menu. Repeat as needed.

BUT Mr. Steele is correct, you're MUCH better off if you redesign your table structure.
 
J

John Vinson

I have a table that contains 30 check boxes in it.
Click to expand...

Then you have an incorrectly normalized table.
This table is only used
when someone removes a vehicle for work. The boxes are used to note if there
is a problem with the vehicle, i.e. Throttle does not work (Check Box).
Click to expand...

So if you ever - EVER - have a fifty-first problem, you will need to
restructure your table, rewrite all queries which involve it, redesign
all your forms and all your reports... not very nice!

A better table design would be to NOT store data (problems) in
fieldnames, but to recognize that you have a many (vehicles) to many
(problems) relationship, with three tables:

Vehicles
VehicleID
<other fields about the vehicle>

Problems
ProblemID <Autonumber Primary Key>
Problem <e.g. "Stuck Throttle">

ProblemReport
VehicleID <link to Vehicles>
ProblemID <Long Integer link to Problems>
ReportDate <Date/Time> <joint three-field primary key>
ReportingPartyID <link to a people table>

If a vehicle had three problems on August 28, this table would have
three records for that vehicle on that date.
I need to create a query that would look for any field that has a box
checked and show it. Is this possible with so many fields. I know the
criteria field is limited to 10, is there another way I am missing?
Click to expand...

You can use "Insert Rows" to insert additional query rows, or go into
SQL view and use a criterion

WHERE Chk1 OR Chk2 OR Chk3 OR Chk4 OR Chk5 OR Chk6 OR ...

etc. using the fifty fieldnames; you don't need Chk1 = True because
it's already either a true or false value.


John W. Vinson[MVP]
 
Top