Odd data structure help

[Jeff Smeker]

I have, over the past few years, developed a Access system that is in
use in our companies engineering, purchasing, and sales departments.
It is a fairly complex system that runs great. So, I am pretty well
versed in Access design (many thanks to all the posters in this group
too, I'm not sure I could have done without).

I do have a situation that has stumped me though. I have a table
listing various parts that are used for a "quote list"
(tblPartsTemplate). The list is getting too long to sort through
manually so I am trying to add some pre-set filters.

In the form where the "quote list" is started I have added four or so
"filters" (CheckBoxes or ComboBoxes) for the user to set-up the
filter.

All the records in tblPartsTemplate relate to two of the various
filters (Filter 1,2). Maybe 25% of the records relate to one other
filter (Filter 3), and another 25% to yet another filter (Filter 4). A
few records relate to all the filters also. Two of these filters are
boolean, and two are user-selectable strings. Right now, I have fields
in this table that basically have a heading of Filter1, Filter2...
ect.

The problem is when I say this: Show me all the records that are the
listed under Filter1. Now, of those records, AND of a particular group
of 25% show me all the records under Filter2. Right here the problem
begins, as I'm sure you saw, I have no way to distinguish "a
particular group of 25%".

I could see splitting the "particular groups" in this table into
different tables, but the records are SO similar otherwise. This table
is used in a ton of other areas of the database and splitting it would
cause major rational problems elsewhere. Also, this "filter" scenario
is the only place the current structure is causing me problems.

Well, I'm not sure that this makes any sense, but I think I need to
look at somehow destinguishing the "particular groups" of records. If
anyone can offer any tips or ideas, it would be much appreciated. And
I'll be sure to post whatever solution I come up with.

Thanks
Jeff

[Pavel Romashkin]

You are saying that

Maybe 25% of the records relate to one other
filter (Filter 3), and another 25% to yet ANOTHER filter (Filter 4)
and
A few records relate to all the filters also.
Now, I am confused. So, when you say "filtering", what you are doing is
selecting records that satisfy the criteria. This is already
accomplished by ANDing the criteria. What you want now is to know if a
particular record is a part of those affected (mostly) by Filter1, or
Filter2, or Filters 3 or 4?
This clearly is ambiguous, if you say that
A few records relate to all the filters also.
For one of these, it is impossible to distinguish which 25% the record
belongs to.
But for those records that are not related to all filters, it seems
simple to do - apply filters in reverse order and form the filtering SQL
such that it returns an extra field, say "FilterKey", which is set to
the filter number. Then, UNION these results and you will have the
distinguished records. However, the ambiguous records will be placed in
the broadest filter.
If you apply the filters, flagging the records, in the forward order
(from the broadest to the narrowest) then ambiguous records will be
places in the narrowest filter they satisfy.

Pavel

Jeff Smeker wrote:
I have, over the past few years, developed a Access system that is in
use in our companies engineering, purchasing, and sales departments.
It is a fairly complex system that runs great. So, I am pretty well
versed in Access design (many thanks to all the posters in this group
too, I'm not sure I could have done without).

I do have a situation that has stumped me though. I have a table
listing various parts that are used for a "quote list"
(tblPartsTemplate). The list is getting too long to sort through
manually so I am trying to add some pre-set filters.

In the form where the "quote list" is started I have added four or so
"filters" (CheckBoxes or ComboBoxes) for the user to set-up the
filter.

All the records in tblPartsTemplate relate to two of the various
filters (Filter 1,2). Maybe 25% of the records relate to one other
filter (Filter 3), and another 25% to yet another filter (Filter 4). A
few records relate to all the filters also. Two of these filters are
boolean, and two are user-selectable strings. Right now, I have fields
in this table that basically have a heading of Filter1, Filter2...
ect.

The problem is when I say this: Show me all the records that are the
listed under Filter1. Now, of those records, AND of a particular group
of 25% show me all the records under Filter2. Right here the problem
begins, as I'm sure you saw, I have no way to distinguish "a
particular group of 25%".

I could see splitting the "particular groups" in this table into
different tables, but the records are SO similar otherwise. This table
is used in a ton of other areas of the database and splitting it would
cause major rational problems elsewhere. Also, this "filter" scenario
is the only place the current structure is causing me problems.

Well, I'm not sure that this makes any sense, but I think I need to
look at somehow destinguishing the "particular groups" of records. If
anyone can offer any tips or ideas, it would be much appreciated. And
I'll be sure to post whatever solution I come up with.

Thanks
Jeff