Long criteria list
I have some data that I need to sort through and create labels with. I have the main source of the data linked, and I have a general group of items (based on 2 fields to narrow it down a bit).
My problem is that it's not "narrow" enough. So I am adding criteria to the query to narrow it down further. unfortunately it's a long list of "Not Like"s as well as a long list of "Like"s. The data will be changing on a monthly basis, which may mean that the criteria would have to be updated too.
Is it possible for the criteria to be based of a table of names? This would make like easier if someone else had to take over and make updates.
Thanks in advance.
You could do this by setting up a search form for your criteria for your query. Look at this tutorial on how to set that up. You might modify this to use a list box instead of a text box.
http://www.datapigtechnologies.com/f...earchform.html
After looking at this, post back if you need additional help
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
I've done that before, and I'm ok with having the user input criteria from a form (actually, part of the database I have going does that for monthly queries on inventory items tracking lease returns).
I guess I should give an example. I will be checking through the data assigned to a specific GEN name. That's easy. But there are more types of items than I need listed under that GEN name. So to narrow it down I have started to make a list of Not Likes in the MODEL field of the query. It's getting a bit long, and there may be times when that criteria changes, as in when we get other eqiupment under that GEN name that do not need to be tracked in this way. I was wondering if I could make a table of the Not Likes, and have the criteria pulled from there. Something like:
Not Like [PrinterExcluder]![toExclude]
That way someone else can add to the PrinterExcluder table if new items don't need to be tracked.
I'm thinking that you will need a subquery with a Not In statement linked to the table listing your items to be excluded. Are you able to post some sample data so that we can try and resolve with out guessing.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Take a look at this QBE example. I think that you should be able to do something similar.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
I tried the in (select...) and that didn't work. ok I guess to give a bit of a sameple to see what I mean:
We are doing special tracking of our Imaging Equipment (This is the specific GEN name to find only these items), but this includes more than just printers (which is what I am try to track).
The information is fed from an ODBC source that tracks all our assests. The one field I can use to scrape away the non-print items is the model. Right now, here is my criteria for the model field:
Not Like "*STAPLER*" And Not Like "*PROJECTOR*" And Not Like "*POWERSHOT*" And Not Like "*CAMERA*" And Not Like "*FAX ACCESSORY*" And Not Like "*Precision*" And Not Like "*USB READER*" And Not Like "*SCANNER*" And Not Like "*CUTTER*" And Not Like "*FINISHER*"
For now that kind of covers it, and I created a similar (slightly longer) list of Like's, but this was shorter. I have also made a table with these exlude names (but stored as *STAPLER*, *PROJECTOR*, etc.
I would like to use this table instead of the Not Likes list that I currently have, incase changes need to be made.
Would you post (attach) your db with sample data in it so I can get a good feel for what you are trying to achieve and then I can possibly give you back a usable solution. In the meantime, I will try to re-create your situation.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Ok, one more time. I have created two queries on the attached. Look at the the List table and then look at the queries. Is this what you are trying to do?
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
ok, kind of there... here I have put in some sample data similar to what I am looking at.
the tbList table has samples of the names I have for equipment models, with both what I want and what I don't (full data). In tbInotlike, I have put in the key words I would like to exclude (as in criteria to cut items from the query) from the equipment list.
the query should output records that do not include the name strings in the Equipment field that are listed in tbInotlike.
I hope that is clear...
For example (using the data I posted), the first item (Laserjet M5035...) should be shown, but the second item (Powershot A40...) should culled.
In the tblLike and tblNotLike, you have not updated for what you want in the db you submitted. It still contains the sample data I put in, but if you follow my example it should work. Did you try this in your production db?
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
I only changed the tbInotLike table and the tbList table to show you what I wanted to exlude from what.Originally Posted by alansidman
The issue is, I do not have a match ID (autonumber) field. Also, looking in the sample I sent back, I'm not looking for an exact match (CAMERA) but a partial match (*CAMERA*) as the Model field could have several types of cameras in the list.
to be to the point, I would have a table : EquipExclude with this in it:
*STAPLER*
*SCANNER*
*FINISHER*
In the source data I would have these in the MODEL column:
LASERJET M5035X MFP PRINT/COPY/SCAN
3000 SHEET STAPLER/STACKER FOR HP
Canon IR2270
BOOKLET MAKER/FINISHER FOR CM6040f MFP
HP Business InkJet 2800
The criteria should look for the partial matches from the EquipExclude list and return the models in black text only:
LASERJET M5035X MFP PRINT/COPY/SCAN
3000 SHEET STAPLER/STACKER FOR HP
Canon IR2270
BOOKLET MAKER/FINISHER FOR CM6040f MFP
HP Business InkJet 2800
I don't need a Like list and a Not Like list. Just one or the other. I'm thinking the Not Like (as in exlude) would be easier to manage
Last edited by jik_ff; 12-09-2010 at 04:30 PM.
I don't have time to test now and will be travelling for the next couple of days but I have an idea if you want to try it out.
I would build a search form and put a List box on the form. This list box would be tied to a table much like a combo box is used for a lookup. If you are unfamiliar with this, then look at this page and watch videos on combo boxes
http://www.datapigtechnologies.com/AccessMain.htm
then look at this video on setting up search forms
http://www.datapigtechnologies.com/f...earchform.html
Using the List box as the input for the criteria, I would then set up the query using a Like function.
Also look at this
http://www.databasedev.co.uk/query_using_listbox.html
Give this some thought and experimentation and I think it will work for you. Right now with the way you have your db set up and wanting to do a Like statement in the critiera, what I have given you in the past will probably not work.
It is also possible that someone else will check in and have a solution for you.
Good luck
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
I'll have to take a look tomorrow, but thanks for the help. Though I can't use your first idea in this job, I think I know another area I can put it to good use.
This is not an end-all situation, and the long criteria string will work. It's just the updating I'm worried about. This work has been passed to me, and I maybe passing it on aswell later on down the road. If nothing else, I'll work in some good documentation,
Thanks again for your time.
Here is another solution that uses the InStr function in an expression. Look at the QLikes query. The expression is not checked so the result of the expression is not shown, but you can see how it works when you open the QBE. Post back if you have any issues. I hope this is what you are looking for.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks