Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Passing a list from Excel to MS Query

Latest post Tue, Feb 10 2009 5:55 PM by AFSkier. 6 replies.
  • Sat, Jan 26 2008 2:57 PM

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 155
    • Points 2,129

    Passing a list from Excel to MS Query

    I've tried this several ways, but no success so far.Sad 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 Freeman Kitchener, ON

    Filed under:
    • Post Points: 21
  • Sat, Jan 26 2008 3:33 PM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,415
    • MVP

    Re: Passing a list from Excel to MS Query

    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.

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    • Post Points: 21
  • Sat, Jan 26 2008 6:17 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 155
    • Points 2,129

    Re: Passing a list from Excel to MS Query

    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?

     

    p1 1
    p2 2
    p3 3
    p4 4
    p5 5
    p6 6
    p7 7
    p8 8
    p9 9

    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 Freeman Kitchener, ON

    • Post Points: 21
  • Sat, Jan 26 2008 7:03 PM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,415
    • MVP

    Re: Passing a list from Excel to MS Query

    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

     

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    • Post Points: 21
  • Sat, Jan 26 2008 9:17 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 155
    • Points 2,129

    Re: Passing a list from Excel to MS Query

    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

    Omar Freeman Kitchener, ON

    • Post Points: 37
  • Fri, Nov 7 2008 5:51 PM In reply to

    • Rich Tatum
    • Not Ranked
    • Joined on Fri, Nov 7 2008
    • Grand Rapids, MI
    • Posts 1
    • Points 5

    Re: Passing a list from Excel to MS Query

    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

    • Post Points: 5
  • Tue, Feb 10 2009 5:55 PM In reply to

    • AFSkier
    • Top 500 Contributor
    • Joined on Tue, Dec 2 2008
    • Salt Lake City, UT
    • Posts 2
    • Points 10

    Re: Passing a list from Excel to MS Query

    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 

     

     

     

    • Post Points: 5
Page 1 of 1 (7 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.