If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Like In" query operation...?
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...? |
#2
|
|||
|
|||
"Like In" query operation...?
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" wrote in message ... 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...? |
#3
|
|||
|
|||
"Like In" query operation...?
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. "John Spencer" wrote: 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... ;-) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
Crosstab Query Help | Becks | New Users | 17 | October 11th, 2005 08:31 PM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
operation must use an updateable query | Brian | General Discussion | 1 | December 31st, 2004 03:30 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |