Problem with filtering by using a lot of check boxes

[dillneus]

hello,

I have some problem with search menu. first i will explain the structure of my search mennu and then the problems that appears...

For creating my search menu i have used tips from that site: http://allenbrowne.com/ser-62.html

So I have Criteria part in Form Header and Results in Detail.
Criteria are devided into parts by using Tabs. So I have Region Tab, Information type Tab, Product Tab and Competitor Tab. All are normally inserted and work ok besides Competitor. But this tab is composed of subform where are the Competitors to choos from check boxes.
Now the case is to filter by all those criteria... but when it comes to competitor i cannot cause competitors are not in the Record Source... I tried to add that but i receive the message like this: Syntax error in FROM clause.
(Competitor Table: IDCompetitor, Competitor1(Yes/No), Competitor2(Yes/No) etc. (22 competitors) ). I suspect that it is caused by the number of fields i am creating in this Record Source... but as i am begginner i dont know for sure.

Does anyone has idea how to use filtering option in my case?
Or maybe you know better way to create search menu, other than on the site i have cited?
thx for any clues.

greetings
Greg

[nico5038]

I guess your "main" table has a Competitor field.
In a case like that I would have a tblCompetitor with a Yes/No field called "Selected".
By having the field set to true, you can JOIN your "main" table to the tblCompetitor and add for the Selected field "True" as the criteria.
This saves the construction of an elaborate WHERE for the competitors.

Getting the idea ?

Nic;o)

[dillneus]

I guess your "main" table has a Competitor field.
In a case like that I would have a tblCompetitor with a Yes/No field called "Selected".
By having the field set to true, you can JOIN your "main" table to the tblCompetitor and add for the Selected field "True" as the criteria.
This saves the construction of an elaborate WHERE for the competitors.

Getting the idea ?

Nic;o)

thx for your response. but i dont get it how to implement in my case.
In mains table i dont have competitor field. Competitor table is connceted with news table in that way:

IDCompetitor ---1-----------------------------------------1------IDNews
Company1 Title
Company2 Source etc.
Comany3
...
Company21
(all yes/no fields)

I created also the query that give me the sum of competitors:

Competitors field : GE, Siemens
GE
Siemens
...

if you could explain me step by step how to enable now filtering that in search menu, i would be grateful.

[nico5038]

You should change your Competitor table by adding the IDNews as a Foreign Key (FK).
Best to use an autonumber as the Unique ID like CompetitorID and when your Competitors are linked to multiple News items, then use:
tblCompetitor with:
CompetitorID
CompetitorName
...etc..
tblCompetitorNews as the relation table with:
CompetitorID
NewsID
and finally ofcourse the tblNews with
NewsID
...

Best to checkout the normalization of tables as described in:
http://www.thescripts.com/forum/thread585228.html

Nic;o)