Easiest way to filter a list of items in a query

[Tambe257]

I have a database of products and I set up a query that will create a table that I can export every time I add new products to the DB. When I do this I can filter it a few ways to get the correct group of products to export, but there will be times where I need to export a list of item#s (item# is one of my fields) that don't relate to each other in any way. What would be the best way to easily filter out a list of items without typing many "Or" criteria in the query?

These come from an Excel file, so I could end up with a list that looks like this:

6110
6113
7104
7105
8165
8667
1437
3722
4074
4089
4116
6527
6090

I'd hate to type out "6110 or 6113 or 7104....". I could write a macro in Excel to concatenate them all with " or " in between, but that seems like an annoying hack. Any suggestions?

[aytee111]

Link to the Excel file and use it in your query.

[Micron]

I need to export a list of item#s (item# is one of my fields) that don't relate to each other in any way.

Even if you link to the spreadsheet, if there is nothing that 'groups' these products, or groups the other products that you don't want to export, then you are beat. If that's the case, you might have a db design problem.

[Tambe257]

Sorry, I think I wasn't clear. The database is fine and all things have proper relationships. What I meant was basically if I have a DB of thousands of records, and I have a list of item#s in Excel that I want to filter from my Database query, is there an easier way than to either literally type, "6011 or 2345 or 8372 or 5392 or 7322...." in the criteria. I know I could import an Excel sheet as a table and find the matches, etc. but that's more work than typing.

The usual way I will do it is to grab all of the new products that I added on a certain day, because I have a "date added" field. Or the products that I've changed prices on, because I have a date field for the price as well. The issue will come up when I have a group of random products that I need to export.

[orange]

Please tell us why you have to use Excel and Access? Perhaps there are options to your business processes to streamline activity.

To exclude/include certain things, you can add such records(ids) to a temporary table, then use SQL to exclude those records in your result.

More info on your need is required for more specific response.

[Tambe257]

I joined this company 6 months ago and am transitioning them from using all Excel for their product/price files to Access. As we transition there will be a period of time that we're maintaining both systems. If I make changes or add products to the Excel file I'd like to just copy the items in question and filter the Access DB down to just those ones. It's not that many or that often, but I thought there might be an easy solution. Typing 25 item numbers into the criteria isn't the worst thing ever, so I can make due.

[Micron]

is there an easier way than to either literally type...

If you have date values for price changes or item additions, that helps. If not and the values are random, then what I don't get is how do you decide what ones you want to grab? Do you open the spreadsheet make random selections without any thought behind it? Do you draw numbers out of a hat and choose those line numbers? That is random. I don't know what random means when used along with part of an explanation that says you have dates as some sort of filter. If there is a logic behind all of the selections, I would not call that random - unless you truly just select whatever items happen to interest you, even if the list is filtered by some sort of date value.

The basic answer to your question IMHO is that the way to automate this somewhat is to build a criteria string in vba, using the IN operator/function. Seems to me I've had issues trying to pass that to a stored query before, so I have to guess that the process would be to assign the entire sql statement (one that uses this operator) to the querydef property of a stored query, or build a record set from such as sql statement and do something with that recordset. Another would be to loop through the spreadsheet and modify a record set. However, this is not something I'd want to do if my plan was to dump the Excel part of the equation. That would be too much work to just throw away a short time later.

[Tambe257]

Hi Micron,
Thanks for the response. In general I will update a bunch of items at once and can grab them easily but the "Date Added" or the "Price Date" if I just change the price. Sometimes we make changes or add a few items here and there over time, and then do one collective push out to our register system. Or we may make a few changes that affect the product records, but aren't price changes, so there's no date record. In a case like this there will be a group of items that I want to update, so I was hoping that I could just paste an array of items into a filter somehow. If it's more complicated than that, then I may as well just type the item numbers in the criteria of the filter. Hopefully I'll be rid of the Excel portion of this process this year and do it all in Access. Thanks for your input.

[Micron]

So either identify the rows by dates, or have a column of values in the sheet (e.g. Y's ) so you can flag them. Then you can loop through those and concatenate them to an In clause. I don't know what the limit is for this clause, but I suppose there is one. Or maybe better yet, just query the linked sheet and return the rows whose flag value is Y.