specialized query

[Guest]

I have a table in Access with the following columns: ID, Code, Material,
Supplier, Green, Yellow, Blue, Red, Orange, Brown, Black. I want to make a
query that will do the following: Displays Code, Material, and Supplier for
all criteria entered (I can do this part) and that asks the user to enter
Criteria 1, then Criteria 2, then Criteria 3, where the criteria are the
different colors listed above. If the user enters Green, Yellow, and Blue
for the criteria, I only want the query to display the rows with data (I'm
using an 'X' for a yes, and nothing for a no) for the criteria entered. In
other words, only to show Green, Yellow, and Blue, and none of the others.
Does anyone know if this can be done?

Thanks!

 

[John Vinson]

I have a table in Access with the following columns: ID, Code, Material,
Supplier, Green, Yellow, Blue, Red, Orange, Brown, Black.

You're having difficulty because your table structure is incorrectly
normalized. Storing data (colors) in fieldnames is NEVER a good idea.

I want to make a
query that will do the following: Displays Code, Material, and Supplier for
all criteria entered (I can do this part) and that asks the user to enter
Criteria 1, then Criteria 2, then Criteria 3, where the criteria are the
different colors listed above. If the user enters Green, Yellow, and Blue
for the criteria, I only want the query to display the rows with data (I'm
using an 'X' for a yes, and nothing for a no) for the criteria entered. In
other words, only to show Green, Yellow, and Blue, and none of the others.
Does anyone know if this can be done?

With a great deal of difficulty: a criterion on each color field such
as

([Yellow]="X" AND ([Criteria 1] = "Yellow" OR [Criteria 2] = "Yellow"
OR [Criteria 3] = "Yellow")

with similar criteria for all the other fields.

MUCH better would be to split this table into two tables: one with ID,
Code, Material, and Supplier; and the other with two fields, ID and
Color. Store the color *as data* in the Color field and then search
it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

That's my fault, I should have written the full headings. The headings are
not for individual colors (I just picked names), but for product
characteristics such as gloss, color, stretch, and strength. Each
code/material has a yes (X) or no (nothing) rating, and I figured that this
data needs to stay in the same table as the material.

I have a table in Access with the following columns: ID, Code, Material,
Supplier, Green, Yellow, Blue, Red, Orange, Brown, Black.

You're having difficulty because your table structure is incorrectly
normalized. Storing data (colors) in fieldnames is NEVER a good idea.

I want to make a
query that will do the following: Displays Code, Material, and Supplier for
all criteria entered (I can do this part) and that asks the user to enter
Criteria 1, then Criteria 2, then Criteria 3, where the criteria are the
different colors listed above. If the user enters Green, Yellow, and Blue
for the criteria, I only want the query to display the rows with data (I'm
using an 'X' for a yes, and nothing for a no) for the criteria entered. In
other words, only to show Green, Yellow, and Blue, and none of the others.
Does anyone know if this can be done?

With a great deal of difficulty: a criterion on each color field such
as

([Yellow]="X" AND ([Criteria 1] = "Yellow" OR [Criteria 2] = "Yellow"
OR [Criteria 3] = "Yellow")

with similar criteria for all the other fields.

MUCH better would be to split this table into two tables: one with ID,
Code, Material, and Supplier; and the other with two fields, ID and
Color. Store the color *as data* in the Color field and then search
it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

That's my fault, I should have written the full headings. The headings are
not for individual colors (I just picked names), but for product
characteristics such as gloss, color, stretch, and strength. Each
code/material has a yes (X) or no (nothing) rating, and I figured that this
data needs to stay in the same table as the material.

I'll stand by my assertion. You have a Many (products) to Many
(characteristics) relationship; a table of Products, and a table of
Characteristics, each related one-to-many to a table of
ProductCharacteristics will be more managable and easier to search
than a table with charactersistics as fieldnames.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps