Hi Andrew,

If you can explain your first idea more detailed, it would be great.
The second idea also sounds interesting, but we already have all our tables populated and therefore we can not change their structures or field content.

Just let me explain the problem one more time for those, who don't read this thread from the beginning.

In our table, which represents building information, we have bunch coded fields, like BuildStyle (Char,2), RoofMat, RoofType, etc. All these fields have code value in it and we have lookups tables, where each code has its description. The situation with amenities is different. Each building may have up to 10 amenities, so field content could be ABCDEFGH..., where each character represents one coded value. Amenity lookups table has description on each code.
Now you can see, that manipulation with this field becomes a little tricky, especially in SQL. Say, I want to select all buldings, which have air conditioner and a balcone, how can I write where condition for this field:
'A' $ amenities and 'B' $ amenities?

And what if ExcludeFlag is checked (though I doubt, what it's applicable for this criterion).

Thanks again.

>Hi Nadya,
>
>There are a couple of approaches I use for this type of thing. The first is a many-many relationship where you have three tables: Customer, Amenity and CustomerAmenity. Ask me if you need some more info on implementing this approach. The great advantage of this approach is its flexibility. You can add any number of amenities and have any number of links between Customer and Amenities.
>
>The second approach is to use a bitmapped approach. I add one or more integer fields to the table and call them "flag fields". Each of the 32 bits in each field represents a known amenity and so the integer value in the field will be made up of a bitmap indicating which amenities are turned on and which are turned off. You can eacily manipulate the fields with the BITSET() function and easily query the field with the BITTEST() function. The advantage of this approach is that it's very compact and queries against the table don't involve joins. OTOH, it's very inflexible. It's difficult to add new amenities - especially when you cross a multiple of 32 barrier.
>
>Cheers,
>
>Andrew
>
>
>>Hi Sergey,
>>
>>Not sure, I understand your idea:
>>

>>mapto='ABCFDG'
>>lcSelList='AG8'
>>?Chrtran(mapto, Chrtran(mapto, lcSelList, ""), "")
>>

>>
>>Let me try to explain again:
>>I'd like to select all records, which satisfy my criterion:
>>
>>In main table (from which I want to select), I have: (example)
>>

>>1   ABCD
>>2   AB
>>3   FG
>>4   AF8
>>

>>etc.
>>
>>User selects A and 8, so records 1,2,4 should be selected.
>>Note, also, what my control allows exclude items, IOW, if I check exclude option, only record 3 should be selected (doesn't contain A or 8).
>>
>>See?
>>
>>Thanks in advance.