multiple criteria selection

[angie]

i have a query with ten different fields. all fields contain vehicles, e.g.
alfa romeo,fiat,chrysler,etc. i have created a form with check boxes for each
vehicle.

i aim for my query to return data according to the check boxes (usually more
than one check box is selected) if the check box values are in any of the ten
fields of my query.

can i achieve that with only one query and if yes how? or do i have to use
more than one query to filter my data?

 

[John W. Vinson]

i have a query with ten different fields. all fields contain vehicles, e.g.
alfa romeo,fiat,chrysler,etc. i have created a form with check boxes for each
vehicle.

Then your table structure IS WRONG.

Storing data - a type of vehicle - in a fieldname is ok for a spreadsheet,
maybe, but it's simply *wrong* for a relational database.

i aim for my query to return data according to the check boxes (usually more
than one check box is selected) if the check box values are in any of the ten
fields of my query.

can i achieve that with only one query and if yes how? or do i have to use
more than one query to filter my data?

If you'll post the actual structure with fieldnames of your table, someone may
be able to suggest a UNION query to search all the fields at once - or a
properly normalized restructuring of your table.

John W. Vinson [MVP]

 

[angie]

table structure, field names: item code, item description, group (e.g.
brembo, bosch,...) , assembly line, (e.g. suspension, brake system,...) and
vehicle 1, vehicle 2, vehicle 3,... until vehicle 10. the reason for this
structure is that an item may have application to more than one vehicle, alfa
romeo and fiat for example. moreover the table "draws" data form an external
application. if you could suggest a different table structure i would greatly
appreciate it.

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

table structure, field names: item code, item description, group (e.g.
brembo, bosch,...) , assembly line, (e.g. suspension, brake system,...) and
vehicle 1, vehicle 2, vehicle 3,... until vehicle 10. the reason for this
structure is that an item may have application to more than one vehicle, alfa
romeo and fiat for example. moreover the table "draws" data form an external
application. if you could suggest a different table structure i would greatly
appreciate it.

Certainly. You're using a relational database, for which one to many
relationships are absolutely fundamental. You have a many to many relationship
here: each part can be used in many vehicles, and each vehicle can use many
parts. The proper relational structure uses *three tables*:

Items
ItemCode <Primary Key, probably, not sure what the code is>
Description
Group <GroupID, link to the primary key of a Groups table>
Line <link to a table of assembly lines>

VehicleTypes
TypeID <Primary Key>
Type <e.g. "Alfa Romeo", "Hupmobile">

ItemApplication
ItemCode <which item is being used>
TypeID <what type of vehicle is it being used in>
<any other fields about how this item is used in this vehicle>


"Fields are expensive. Records are cheap". If one of something is related to
many of something else, you will pretty much always want to store one record
in a table for each instance of that relationship, rather than adding more
fields!

John W. Vinson [MVP]

 

[angie]

if i understand correctly i will have three tables. table 1 will have item
data (code=primary key, description,etc. table 2 will have vehicles (vehicle
id, description) and table 3 will contain in my case item code (duplicate
values=ok ) and vehicle applicable to each code.

as i have told you i draw the data from an external source and to achieve
this structure i will have to perform ten imports to ten different tables
automated through a macro. that is import 1=item data, import 2 = vehicle 1,
import 2 = vehicle 2... until vehicle 10. i will then create a separate table
and i will append all data from vehicles tbls to a single table. is my
thinking correct?

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

if i understand correctly i will have three tables. table 1 will have item
data (code=primary key, description,etc. table 2 will have vehicles (vehicle
id, description) and table 3 will contain in my case item code (duplicate
values=ok ) and vehicle applicable to each code.

as i have told you i draw the data from an external source and to achieve
this structure i will have to perform ten imports to ten different tables
automated through a macro. that is import 1=item data, import 2 = vehicle 1,
import 2 = vehicle 2... until vehicle 10. i will then create a separate table
and i will append all data from vehicles tbls to a single table. is my
thinking correct?

Well, you don't actually need ten different imports. You could import your
external data into a "wide-flat" table in your current structure, and then use
two "Normalizing Union Queries" to migrate the data into the properly
normalized structure. What are the actual contents of the Vehicle1, Vehicle2
etc. fields - vehicle names? Are the fieldnames you posted hypothetical
examples or the actual table fieldnames?

The Union query needs to be constructed in the SQL window, not the query grid.
It would be something like

SELECT ItemCode, Vehicle1 FROM wideflat WHERE Vehicle1 IS NOT NULL
UNION
SELECT ItemCode, Vehicle2 FROM wideflat WHERE Vehicle2 IS NOT NULL
UNION
SELECT ItemCode, Vehicle3 FROM wideflat WHERE Vehicle3 IS NOT NULL
UNION
SELECT ItemCode, Vehicle4 FROM wideflat WHERE Vehicle4 IS NOT NULL
UNION
<etc etc>

to get all non-NULL vehicle fields associated with their corresponding item.
You could then base an Append query on this UNION query to add records (from
zero to ten per part, depending on the contents of the ten fields) into your
tall-thin table.

John W. Vinson [MVP]

 

[angie]

i have tried that and it works fine! thank you!

now i have a query (from applications tbl) with item code and vehicle fields
(two fields in total). i want to have the query return only data according to
the check boxes selected in my form. the form consists of many check boxes
e.g. alfa romeo, hyundai....

how can i set my criteria? do i have to enter multiple OR criteria or is
there another way?

i am looking forward to your advice!

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

now i have a query (from applications tbl) with item code and vehicle fields
(two fields in total). i want to have the query return only data according to
the check boxes selected in my form. the form consists of many check boxes
e.g. alfa romeo, hyundai....

how can i set my criteria? do i have to enter multiple OR criteria or is
there another way?

Well... checkboxes aren't the ideal interface in this case. There are two ways
to handle this user interface choice, none of them ideal. One would be a query
like

SELECT ItemCode, Vehicle
FROM Applications
WHERE (Vehicle = "alfa romeo" AND Forms!yourform!chkAlfaRomeo = True)
OR (Vehicle = "hyundai" AND Forms!yourform!chkHyundai = True)
OR (Vehicle = ...

through all ten checkboxes; or write VBA code to poll through the checkboxes
and construct a SQL string in code.

One BIG downside of the checkbox approach is that the user could check two, or
three, or ten checkboxes! Which vehicle do you want in that case? Any of them?
all of them?

Could you consider a Listbox showing all the vehicle names instead? The user
could again select any one (or, with a multiselect checkbox, multiple)
vehicles with a mouseclick. Is the choice of a checkbox as the type of control
obligatory?

John W. Vinson [MVP]

 

[angie]

well i have already been working with a combo box but the problem is that the
user can have only one vehicle selected each time. that is why i have created
the form with all these check boxes. the user can select one, two, neither or
all of the check boxes. do you think i should set at least one selection
obligatory?

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[John W. Vinson]

well i have already been working with a combo box but the problem is that the
user can have only one vehicle selected each time. that is why i have created
the form with all these check boxes. the user can select one, two, neither or
all of the check boxes. do you think i should set at least one selection
obligatory?

That's even MORE work.

You've rejected a continuous Subform with one vehicle per row, and the listbox
idea? If you really want to have ten unbound checkboxes and write VBA code to
parse through them, determine if at least one is selected, and append records
to the ItemsVehicles table, you can do so... but it hardly makes it simple for
you (deleting records becomes a major pain for example).

It's up to you!

John W. Vinson [MVP]