"Like In" query operation...?

[Mark Burns]

Just a question - not really a pressing issue, but I was wondering something
based upon somebody else's question in the .forms section.

Field Data Sample:
Field1: Text(50)
Sales
Yadda
Non-Sales
Low Sales
High Sales
Bonus Sayes
Yadda Yadda
Overstock Sales
Sales - East
Sales - West
Non Sales Northwestern
etc.,.

If, when querying this data you would like to Query for all the "sales" data
("Non-Sales" being, by definition not sales data). What is the best way to go
and get this sort of data.
a Query Expression of: Like "*Sales*" (/ Alike "%Sales%") would not do
because it would retrieve those pesky "Non-Sales" rows. Now, we could easily
exclude them with a specific '... and [Field1] <> "Non-Sales"...' condition
expression, but that can be cumbersome, hard to maintain, and inaccurate over
time.
What would be the best way to query in a situation like this?
I was thinking about a combined Like/Alike and IN operator something along
the lines of:
(Field1 Like in ("* Sales", "Sales *") )
and (field1 not like in ("non Sales *", "No*Sale? *")) .
....or is there some Regular Expressions sort of way I'm not thinking of
here...?

 

[John Spencer]

If the field repeats (you have many Overstock Sales, many Non-Sales, etc) I
would add an additional tables with a field to flag if the value as a Sale
or not. Then you only need to join this new table and set criteria on the
IsThisASale Field.

You cannot combine the Like operator with the In operator. As a matter of
fact, I know of no way to combine any of the operators.

Table: LookupField1Category
Field: Field1 Text(50) Primary Key (Unique set of values from your
Sample)
Field: IstThisASaleField Yes/No

The concept can be expanded to use a table of categories which just contains
categories and a table of "salesCategories" which acts as a join table
between your original table field and the categories table.

 

[Mark Burns]

John,

Yes, I know we can't currently use a combined "Like In"(/"Alike In")
operator presently. It was more of a wishful thinking kind of musing.

I agree with your approach with the "This is a Sales Item" cross-reference
table, but sometimes, (like when a client hands you an ill-planned,
non-IT-developed database with a request like "can you just make this report
work quick?", you just sort of wish you _could_ do a "like In" sort of query
to quickly dash off at least a somewhat easy one-off solution without the
"mess" of building cross-reference tables & such.

If the field repeats (you have many Overstock Sales, many Non-Sales, etc) I
would add an additional tables with a field to flag if the value as a Sale
or not. Then you only need to join this new table and set criteria on the
IsThisASale Field.

Yeah, except (as I mentioned above) if this is a "customer-made"
non-IT-designed database, you can wind up with all kinds of free-typed data
in a field like that. While you or I can maybe make things work with a
custom-made matching table _now_ but 6 months from now, after even more
people have had the chance to create even more typos in the data field, your
list won't match up with the new reality in the data, and now there's an
extra, "new" data-list-maintenance task in the mix. (One that "the customer"
may or may not "know" about - even if told explicitly - or remember to do
anything about...etc.,.. All they'd know is "Hey. this report isn't
right...is it?").

You cannot combine the Like operator with the In operator. As a matter of
fact, I know of no way to combine any of the operators.

Yup - see above - it's a "wish".

Table: LookupField1Category
Field: Field1 Text(50) Primary Key (Unique set of values from your
Sample)
Field: IstThisASaleField Yes/No

The concept can be expanded to use a table of categories which just contains
categories and a table of "salesCategories" which acts as a join table
between your original table field and the categories table.

yup. But see, you're thinking like an IT/Database guy... ;-)