Can I use an SQL statement to extract data from sheet A + sheet B and dump in in sheet C using only Excel

I have data like this in Excel

table Cost_per_period
---------------------
ProjectId
FaseID
Period
Percentage

table cost_per_partner_per_fase
-------------------------------
ProjectID
FaseID
PartnerID
Amount

table partners
--------------
PartnerID
name

Here's the output I want.

                 2012             2013                2014     2015
Project  fase    jan  f开发者_如何学Goeb ... dec  Q1    Q2   Q3   Q4  wholeY  wholeY
------------------------------------------------------------------------
A310     1       100k 20k     10k  100k   -   10k  10k 1000k   2000k
A310     2       110k   -     20k   99k   -   40k  50k 5000k   3000k
......

To combine this data, I'm thinking of doing a SQL statement like

SELECT cp.projectID, cp.faseID
  , case when cp.period between '2012/01/01' and '2012/01/31' 
         THEN sum(cpf.amount)*cp.percentage as jan2012 end
  , case when ..... as feb2012 end
  , case .......
FROM cost_per_period as cp
INNER JOIN cost_per_partner_per_fase as cpf 
  on (cp.postjectid = cpf.projectid) and (cp.faseid = cpf.faseid)
GROUP BY cp.Projectid, cp.faseid
ORDER BY cp.ProjectID, cp.FaseID

Can I do this using only Excel?, I'm using excel 2007

---

Here's the simplest of queries against Excel which may get you started:

Sub test()
  Dim rs As Object
  Set rs = CreateObject("ADOR.Recordset")
  rs.Open _
      "SELECT CompanyName FROM Customers;", _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Tempo\db.xls;" & _
        "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    Sheet3.Range("A1").CopyFromRecordset rs
  End Sub

---

This is taken from a macro recorded in Excel 2007 that works for me. You can modify the data source and the SQL query to suit your need.

ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _
    , "Data Source=C:\RM.xlsm;" _
    , "Jet OLEDB:Engine Type=37"), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("SELECT * FROM [Sheet1$A1:B30]")

End With

---

If the data is already in Excel, you can't run an SQL query against it. You'd only be able to run that kind of code against an SQL database (eg if you had the data in Access to start with).

However, you can write an equivalent function in VBA and run that in Excel.

I also found this plug in that would let you execute SQL against Excel data: http://www.querycell.com/ but it's not free and I have no idea how good it is.