Passing a list from Excel to MS Query

I've tried this several ways, but no success so far. Is it possible to pass multiple values from an Excel cell as parameters in a Data query?

In MS Query, this looks like "IN (1,2,3,4,5)". Or, calling a parameter looks like "[List]" which can be tied to one worksheet cell. However, I've not been able to combine the two yet. I also have the need to do the opposite, as in "show me everything but the items in this list." "NOT IN (1,2,3,4,5)"

My primary motivation is to allow someone else to maintain the spreadsheet. Currently, every time the list changes, I need to go into MSQuery and manually maintain it. Since this is a report that ties into our sales compensation verification procedures, I'd prefer that it would be easier for others to update the spreadsheet.

The only solution I've come up with so far is to create multiple parameters in MSQuery tying to multiple individual cells, but I expect this list to have 12 to 20 items. This could be quite cumbersome. 

Thanks for any ideas on this. Even if it is to say it's just not possible.

 Omar

Omar

Coincidentally, I posted this yesterday. It uses two and a 'between' operator, but will hopefully let you move forward. Let us know if it is still a problem.

That's really a nice tutorial. I wish I had seen something like that some four years ago when I first started building data queries. I will keep it in mind if I need some more review or need to train one of my co-workers on MS Query.

Using the Between operator does something I have normally been doing with two individual parameters. I would have one ">=[startdate]" and another "<=[enddate]" parameter. I have dozens of spreadsheets using that basic method of narrowing down records based on dates. I'll experiment with the Between operator for that.

However, it looks like you are still stuck with one cell per parameter. Do I end up having to build a table for this? What if I don't need all the slots I've created? Can I leave some blank, or do I just put in duplicates?

I'm not at my work computer now so I can't test this right away, but it looks like I would have to build a query with a statement such as this (using the exclusion list I mentioned in my first post):

" not = [p1] and not = [p2] and not = [p3]..."

Despite using sequential numbers in my examples, I'm dealing with non-sequential exclusions since I'm using employee numbers. Essentially, I'm trying to get a report that groups all the non-commissioned sales together to verify that none of them should have gone to a commissioned salesperson (it's very easy in our system for an administrator to type an incorrect number and attribute a sale to the wrong person). I need to exclude all the commissioned people as they each get their own report and it's easier to manually scan a shorter list for errors. 

Anyway, thanks. Unless another idea comes up, I'll probably try to make this multiple parameter and table idea work.

Omar 

Omar

Certainly I suspect you will need to have multiple cells, which would not be ideal.

I would suggest using a Pivot Table rather than the 'flat data' and then you could set up the employee IDs as a page field for example and filter out some.

For help with Pivot Tables, there is some info on here, or at www.contextures.com

Thanks for your responses. I wanted to make sure I wasn't overlooking something with the query itself.

I've been avoiding using a pivot table. Part of the test is to do a vlookup to another query result table and verify that values in one table exist in another table. Using formulas to do that has been beyond my current knowledge. I may have to bite the bullet and just get it done!

Too often when I've messed with pivot tables with my data source, I seem to run into road blocks. Eg, once I wanted to group and summarize by month. After many failures, I finally deduced that my data was returning some blank dates, so the pivot table couldn't be grouped. Or, the problem might be that I just want it to look better printed out, and my knowledge of pivot tables is limited there. It's just too easy to jump back to my comfortable old habits I guess (if you call setting up sumif or array formulas comfortable!)

Thanks again for your advice.

Omar

I've been trying to do something similar. I have a database query that returns a list of items from several categories. One of the paramaters I want users to be able to select is whether or not to exclude marketing items in the list. I'm already excluding other items. So I want to offer the ability to either exclude one set, or another set of rows. So, either Not In('a','b',c') or Not In('a','c').

Problem is, the In() and Not In() function only seems to work with a stored paramater when there is only one item in the array. This makes no sense.

I've tried using values in the cell without quotes and comma delibers, with them, with double quotes, and etc. To no avail. The in() function will only accept a paramater with a single value.

I know I can skin this cat other ways, but this is the sensible way to do it, and MSQuery is being un-sensible. :)

Ideas?

Rich

I had the same issue. I wanted to import a dynamic list into my query's IN statement for my output to excel.  I found a way to work around this using MS Access instead of MS Query.

In Access, link to your source Excel list (can be in the same workbook for your output query).  Then create your query in Access.  Add the IN statement to reference your Excel source data list.

Sample SQL

SELECT DISTINCT b.ID, Sum(b.SALES) AS [Sum of Sales]

FROM [DIV 1] AS b

WHERE b.ID In (SELECT DISTINCT a.[source_id] FROM [excel_list] AS a WHERE a.[source_id] is not null)

GROUP BY b.ID;

Just like you imported using MS Query, instead import from MS Access.  To import from MS Access go to Data, From Other Sources, From MS Query, MS Access Database (ODBC), select your Access database, return data to Excel.

This solved my issue, I hope it helps you solve yours.

Kevin