SQL / Excel Query Parameter with a JOIN

Question

I am learning how to use parameters in an excel-driven SQL query (in fact I am still learning SQL in general). Thanks to the nice people that helped me build my query to modify the results as I need, I want to take this a step further and supply a parameter in Excel to filter the results.

Here is my query:

SELECT 
    fun.FUNCTION_ID
    ,COALESCE(fun.parent_function, fun2.function_id) as PARENT_FUNCTION
    ,fun.MODULE_ID
    ,fun.DESCRIPTION
    ,fun.FUNCTION_PURPOSE
    ,fun.PB_OBJECT
    ,sec.GROUP_ID
    ,sec.ACCESS_LEVEL
from 
    MODULE_FUNCTION fun

    LEFT JOIN MODULE_FUNCTION fun2
    ON fun.function_id = fun2.function_id
    AND fun2.function_id IN (SELECT parent_function FROM MODULE_FUNCTION)

    LEFT OUTER JOIN FUNCTION_SECURITY sec
    ON fun.FUNCTION_ID = sec.FUNCTION_ID
    AND sec.GROUP_ID = 'GROUP_NAME'

What I need to do is allow people from a team to run this query in the excel sheet and supply their group name for the "GROUP_NAME" in the second JOIN. Unfortunately I cannot use the syntax WHERE (sec.GROUP_ID = ?) (found here) as I need to pull all results from the MODULE_FUNCTION table and only insert results on the right from the FUNCTION_SECURITY table when there is a match on the supplied group (leaving null when there is no match).

When I try to use AND (sec.GROUP_ID = ?) at the end I get a "Invalid Parameter Number" in Excel. From what I have gathered, the "?" can only be use with WHERE (and works find for me in test queries).

I have tried many things, including declaring a @parameter, but no avail.

I'm tempted to try this technique but I'd like to avoid VB if possible.

Joseph · Accepted Answer · 2012-12-13 14:26:48Z

4

I know you said you want to avoid VB, but it isn't too complicated for what you want to do.

You can have the sheet have a cell for the group name, then a button that calls a macro where you would change the sql query to adjust for the group_id.

Something like:

Dim sql As String

sql = "select ... from ... and sec.GROUP_ID = '?'"
sql = Replace(sql, "?", Worksheets("Analysis").Range("A1").Value)

With ActiveWorkbook.Connections("connection name").OLEDBConnection
    .CommandText = sql
    .Refresh
End With

Where:

Worksheets("Analysis").Range("A1").Value

is the Group_ID. You can set this to a specific cell in any sheet in your workbook. I would create a button right next to it called "Refresh table" or something like that.

If you already made a table that links to a database, then there is a connection object in Excel. Go to the Data tab, then click "Connections". A new window will pop up. Find the connection that matches to the SQL query. Click on that connection and click "Properties" then change the connection name to something easy (it's usually some long name based on the server/table you connect to). Use that for the

ActiveWorkbook.Connections("connection name")

section.

Link to create button on worksheet and link to macro:

http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010236676.aspx

edited Dec 13, 2012 at 14:26

answered Dec 11, 2012 at 15:47

Joseph

5,2001 gold badge27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

alteredNate Over a year ago

Is it really that simple? I am mystified by VB but this seems easy. I will try later and let you know.

Joseph Over a year ago

@alteredNate well, it can be confusing at first, but it's not so bad once you get into the swing of things. I wasn't sure how much you knew about this, so if you need more help with implementing my answer, please let me know. I'll be happy to explain in greater detail.

alteredNate Over a year ago

Well I know very little actually :) I was about to dive into your code and the one I linked to and try and understand. If you are up for some more detailed explination for my specific case, I would certianly be grateful! Between SQL queries, pivot tables, and database management I'm learning alot at the moment, and VB wasn't initially on that list! ha!

Joseph Over a year ago

@alteredNate Got it. I updated the answer to include more info and a link to how to create a button that is linked to a macro. Also, YouTube is a great place to find easy videos on starting with VBA. example: youtube.com/watch?v=zt3Yan14ERs

alteredNate Over a year ago

Thanks for that! I have followed everything as best as I can, I got the connection name, sheet name containing the value that I want, and I wrapped your VB into a Sub to make it a macro to link to the button. I carefully inserted my SQL into the string on one long line, but I get a "Type Mismatch" error. (I tried the VB debug and apparently it gets stuck on the .CommandText step ("Run-time Error '13' - Type Mismatch). I know it's not the connection string or the sheet name because if I fake those I get a different error. Any idea?

|

Collectives™ on Stack Overflow

SQL / Excel Query Parameter with a JOIN

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related