quering all fields

[jim]

Please help...............
My Company Details Table has 4 fields Class 1 to 4
each with a different entry from a drop down list which is a single table with only the 1 field (ProductFilterClass)

My Search Form has the 4 class fields on the main form and displays the resorts from the class fields in a subform

How can I make each class field search all 4 fields in the Company Details Table, for whichever option is chosen from the drop down list e.g

Search form class option 1=motors Motors appears in the class 3 and class 1 fields in the Company Details Table
Search form class option 2=fixings Fixings appears in the class 1, 2, 4 fields in the Company Details Table

And display the companies names in the subform if it finds motors or fixings or both next to the company name in 1 of the 4 class fields

Got stuck on this only just started out design databases please help.

 

[Tim Ferguson]

My Company Details Table has 4 fields Class 1 to 4,
each with a different entry from a drop down list which is a single
table with only the 1 field (ProductFilterClass).
....

How can I make each class field search all 4 fields in the Company
Details Table, for whichever option is chosen from the drop down list
e.g.

The best answer is to revisit your table design. It's an error to design
fields like Class1, Class2, Class3, Class4, and so on -- they should be in
a table callled ClassEntries (or whatever this is meant to be) thus:-

*CompanyID FK references Companies
*ClassNumber
Filter FK references ProductFilterClass

so that it has one record for each ClassNumber for each Company. It is then
easy to search, easy to maintain, faster, safer, detects duplicates and so
on and so on. You might find it helpful to read up on Normal Theory and
normalisation processes.

HTH


Tim F