• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

SQL Qury into Excel

I need to query a SQL Database from Excel, and insert that data into the Excel sheet, filtered by other columns in the Excel sheet.

For a starter example, I will start with a, Excel sheet that has dates with a header called "Pay_PD_END_Date" (same name as the sql column), in column A.

I want this query on Column B:

SELECT     COUNT(EMPL_ID) AS Expr1
FROM         DELTEK.EMPL_EARNINGS
GROUP BY PAY_PD_END_DT, PAY_PD_CD
HAVING      (PAY_PD_END_DT =CONVERT(DATETIME, '***', 102)) AND (PAY_PD_CD = 'W')

*** WOULD EQUAL THE VALUE IN CELL A

I want this query in Column C

SELECT     COUNT(EMPL_ID) AS Expr1
FROM         DELTEK.EMPL_EARNINGS
GROUP BY PAY_PD_END_DT, PAY_PD_CD
HAVING      (PAY_PD_END_DT = CONVERT(DATETIME, '***', 102)) AND (PAY_PD_CD = 'B')
*** WOULD EQUAL THE VALUE IN CELL A


Your help is greatly appreciated.
Thanks
0
mikeewalton
Asked:
mikeewalton
2 Solutions
 
Marten RuneCommented:
For starters check out: http://www.familycomputerclub.com/excel/excel_import_data_from_access.swf.html

Try it yourself but with sql as a datasource.

Its pretty selfexplaining. If you have further questions, just specify here!

Regards Marten
0
 
Robberbaron (robr)Commented:
i would have to do it by User Functions, which is probably not the most efficient.  And the DB connection setup would need to be manual.

can you do it as an old-style Excel database lookup, where the source range is the query output of all dates ?
''''---------Untested-------------
Dim conn As Connection

Sub SetupDBConn()
    conn.Open ConnectionString, UserId, Pwd
End Sub
Function PAY_PD_CD_W(datex As Range)
    sqlq = _
    "SELECT     COUNT(EMPL_ID) AS Expr1 " & _
    "FROM DELTEK.EMPL_EARNINGS " & _
    "GROUP BY PAY_PD_END_DT, PAY_PD_CD " & _
    "HAVING      (PAY_PD_END_DT =CONVERT(DATETIME, '" & datex.Value & "', 102)) AND (PAY_PD_CD = 'W')"
    
    Dim rst As Recordset
    If IsNull(conn) Then SetupDBConn
    
    Set rst = conn.Execute(sqlq)
    
    PAY_PD_CD_W = rst.Fields(0)
    
End Function

Function PAY_PD_CD_B(datex As Range)
    sqlq = _
    "SELECT     COUNT(EMPL_ID) AS Expr1 " & _
    "FROM DELTEK.EMPL_EARNINGS " & _
    "GROUP BY PAY_PD_END_DT, PAY_PD_CD " & _
    "HAVING      (PAY_PD_END_DT =CONVERT(DATETIME, '" & datex.Value & "', 102)) AND (PAY_PD_CD = 'B')"
    
    Dim rst As Recordset
    If IsNull(conn) Then SetupDBConn
    
    Set rst = conn.Execute(sqlq)
    
    PAY_PD_CD_B = rst.Fields(0)
End Function

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now