How to have ms query reference the SQL on an excel worksheet

How can I have MS Query reference SQL on an excel worksheet?  I would like to have the parameter for my union query where I have to enter a range for each statement ( between '20100408' and '20100414').  If I enter my SQL in the MS Query SQL window it cannot be represented graphically.  Then when I try to add the paremeter through data connections...  I can only enter the parameter for my first select statement.

I am using Excel 2007 and am open to a VBA alternative, but I will need handholding on the execution(s)

Thanks a million
kgittingerAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Try this function - you just need to pass the two ranges to it and it will return the completed SQL string:



Function BuildSQL(rngParm1 As Range, rngParm2 As Range) As String
Dim strSQL As String
strSQL = "SELECT dbo.Demo.EmployerID, dbo.Demo.Division, dbo.Demo.FName, dbo.Demo.LName, " & _
        "dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name, dbo.Trans_POS.PlanID, " & _
        "dbo.Trans_POS.AccountType, dbo.Trans_POS.EDate, dbo.Trans_POS.SettleDate, dbo.Trans_POS.ReImbDate, " & _
        "dbo.Trans_POS.TransAmt, dbo.Trans_POS.ReImbMeth, dbo.Trans_POS.SettleSqNum as ChkTrcNum, dbo.Trans_POS.ChkReIssue " & _
        "FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_POS ON dbo.Demo.EmployeeID = dbo.Trans_POS.EmployeeID " & _
        "AND dbo.Demo.EmployerID = dbo.Trans_POS.EmployerID " & _
        "WHERE (dbo.Trans_POS.SettleDate BETWEEN '" & rngParm1.Text & "' AND '" & rngParm2.Text & "') "
strSQL = strSQL & "Union ALL SELECT dbo.Demo.EmployerID, dbo.Demo.Division, " & _
        "dbo.Demo.FName, dbo.Demo.LName, dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name, dbo.Trans_Man.PlanID, " & _
        "dbo.Trans_Man.AccountType, dbo.Trans_Man.EDate, dbo.Trans_Man.SettleDate, dbo.Trans_Man.ReImbDate, dbo.Trans_Man.TransAmt, " & _
        "dbo.Trans_Man.ReImbMeth, dbo.Trans_Man.ChkTrcNum, dbo.Trans_Man.ChkReIssue FROM dbo.Demo " & _
        "RIGHT OUTER JOIN dbo.Trans_Man ON dbo.Demo.EmployeeID = dbo.Trans_Man.EmployeeID AND dbo.Demo.EmployerID = dbo.Trans_Man.EmployerID " & _
        "WHERE(dbo.Trans_Man.ReImbDate BETWEEN '" & rngParm1.Text & "' AND '" & rngParm2.Text & "')"
BuildSQL = strSQL

End Function

Open in new window

0
 
kgittingerAuthor Commented:
oops  Here is my sql
SELECT
dbo.Demo.EmployerID, 
dbo.Demo.Division, 
dbo.Demo.FName, 
dbo.Demo.LName, 
dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name,
dbo.Trans_POS.PlanID, 
dbo.Trans_POS.AccountType, 
dbo.Trans_POS.EDate, 
dbo.Trans_POS.SettleDate, 
dbo.Trans_POS.ReImbDate, 
dbo.Trans_POS.TransAmt, 
dbo.Trans_POS.ReImbMeth, 
dbo.Trans_POS.SettleSqNum as ChkTrcNum, 
dbo.Trans_POS.ChkReIssue
FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_POS ON dbo.Demo.EmployeeID = dbo.Trans_POS.EmployeeID 
AND dbo.Demo.EmployerID = dbo.Trans_POS.EmployerID
WHERE (dbo.Trans_POS.SettleDate BETWEEN '20100301' AND '20100331')


UNION ALL
SELECT dbo.Demo.EmployerID, 
dbo.Demo.Division, 
dbo.Demo.FName, 
dbo.Demo.LName, 
dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name,
dbo.Trans_Man.PlanID, 
dbo.Trans_Man.AccountType,
dbo.Trans_Man.EDate, 
dbo.Trans_Man.SettleDate, 
dbo.Trans_Man.ReImbDate, 
dbo.Trans_Man.TransAmt, 
dbo.Trans_Man.ReImbMeth, 
dbo.Trans_Man.ChkTrcNum, 
dbo.Trans_Man.ChkReIssue
FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_Man ON dbo.Demo.EmployeeID = dbo.Trans_Man.EmployeeID 
AND dbo.Demo.EmployerID = dbo.Trans_Man.EmployerID
WHERE(dbo.Trans_Man.ReImbDate BETWEEN '20100301' AND '20100331')

Open in new window

0
 
RichardSchollarCommented:
Hi

On a fresh worksheet, can you record a macro of you creating a new data table from MSQuery using the SQL you showed above?  Please post the resulting VBA code into a reply - this will provide all the connection string info for your database (presumed to be SQL Server) and allow us to craft a solution for you.  You do need to use VBA for this.

Richard
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
Hi,

These links should help you, straight from the horses mouth, so to speak

http://office.microsoft.com/en-us/excel/HP051995481033.aspx

expand  "Use data from a cell as a parameter value"
0
 
kgittingerAuthor Commented:
Thank you for the comments and I am sorry to be so dense...
1) I do not know how to create a macro to create a table...  I do not have administrator rites (The server is on an outside site) on the server, so I cannot create a table...
2) since my data is not represented graphically, I cannot edit the paremeter as customary (this is the root of my problem)
it looks like rorya has a good jump on things for me.  I just need some more help...  Do I add this VBA in a Module?  Then how do I pass the range to the function?  I can see rngParm1 (as my 1st Paramater) and rngParm2 (as my 2nd Parameter) but I am confused as to why they are both called Range - Although I am sure you are right, I just don't understand

0
 
Rory ArchibaldCommented:
I am assuming that you have a querytable already set up (per your other posts) that points at the database. You would need to copy that function into a new module in your workbook, then add a macro that uses something like the code below. Without knowing anything about your workbook it's impossible to provide more specific code! :)
Rory


PS The code assumes you are using XL2007.





Sub UpdateQT()
   Dim qt as Querytable
   Dim wks as worksheet
   set wks = sheets("Sheet name")
   Set QT = wks.Listobjects(1).Querytable
   QT.Commandtext = BuildSQL(wks.range("A1"), wks.range("A2"))
   QT.Refresh
End Sub

Open in new window

0
 
kgittingerAuthor Commented:
Thanks I will try it, I must drive to the office.  I have written my SQL in studio and am tring to get it into excel...

More later
0
 
stevericeCommented:
0
 
kgittingerAuthor Commented:
I am unable to create a view.  I was able to swap around my statements and create the paremters as you instructed in my previous question, but it does not work for htis example (different SQL server/Site)??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.