| re: Odd data structure help
You are saying that
[color=blue]
> Maybe 25% of the records relate to one other
> filter (Filter 3), and another 25% to yet ANOTHER filter (Filter 4)[/color]
and
[color=blue]
> A few records relate to all the filters also.[/color]
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
[color=blue]
> A few records relate to all the filters also.[/color]
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:[color=blue]
>
> 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[/color]
| 
