Running queries in Excel with SQL statements

[cssamerican]

I am using Excel as a small database program. I am using the DOA approach to access a worksheet with the data located on it. The data is a collection of text, quantity and cost fields similar to this.

Last_Name...First_Name.....Budget_Code....Amount...Quantity1...Quantity2... Quantity3
Doe.........John...........1110...........$210.00..50..........60.......... 65
Doe.........Jan............1110...........$105.00..25..........35.......... 10
Doe.........John...........1115...........$300.00..40..........30.......... 51
Doe.........John...........1110...........$100.00..20..........40.......... 97

If I choose to pull the data for Budget_Code 1110 I want the output to look like this.

Last_Name...First_Name.....Budget_Code....Amount....Quantity1...Quantity... Quantity3...(Q1+Q2+Q3)*.05
Doe.........John...........1110...........$300.00...70..........100........ 162.........$16.60
Doe.........Jan............1110...........$105.00...25..........35......... 10..........$3.50

Is there a simple SQL statement that will give me this, or will I have to create virtual tables and run multiple Sql statements on those tables? If the later is the case could someone here please point in the right direction on how to go about doing that with VB in Excel.

[austenr]

Maybe you could use VLOOKUP as an alternative.

[Bob Phillips]

Sub a()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long, j As Long
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"
Set oRS = CreateObject("ADODB.Recordset")
sSQL = "SELECT Last_Name, " & vbNewLine & _
" First_Name, " & vbNewLine & _
" Budget_Code, " & vbNewLine & _
" SUM(Amount), " & vbNewLine & _
" SUM(Quantity1) AS 'Quantity1', " & vbNewLine & _
" SUM(Quantity2) AS 'Quantity2', " & vbNewLine & _
" SUM(Quantity3) AS 'Quantity3' " & vbNewLine & _
"FROM [Sales$] " & vbNewLine & _
"WHERE Budget_Code = 1110 " & vbNewLine & _
"GROUP BY Last_Name, First_Name, Budget_Code"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure we received data.
If Not oRS.EOF Then
For i = 1 To oRS.fields.Count
Cells(1, i).Value = oRS.fields.Item(i - 1).Name
Next i
Range("A2").CopyFromRecordset oRS
For j = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(j, i).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])*0.5"
Next j
Else
MsgBox "No records returned.", vbCritical
End If
' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing
End Sub

[cssamerican]

Xld, my VB code is quite different from yours, but the SQL works. Thanks a bunch.

Is there a way to mark this thread as solved???