How to set up SQL Queries for a range of cells

[megnin]

I have a quartly report worksheet that pulls counts of answers to audit questions into a table that has:
Questions in column B, under Month 1 "Y" and "N" are in columns C and D, respectively, under Month 2 "Y" and "N" are in columns E and F and under Month 3 "Y" and "N" are in columns G and H. So it looks like this:


_______ B ____ | C | D | E | F | G | H |
1
2
3
4 _____________|__Jul__|__Aug__|__Sep__|
5 __ Questions_|_Y_|_N_|_Y_|_N_|_Y_|_N_|
6 Does 1=1?____| 3 | 7 |10 | 5 | 1 | 4 |
7 Is rope long?| 2 | 8 | 6 | 9 |11 | 8 |


I was using this SUMPRODUCT formula in each cell to return the number of each "Y" and "N" answer:
=SUMPRODUCT((dynaMonth=FSET_Report!D$4)*(dynaOneStop=FSET_Report!$I$1)*(OFFSET(dynaRange,0,MATCH($Q6,FSET!$1:$1,FALSE)-1)=C$5)*1)
Where D$4 was the month, $I$1 was the facility or "Center",$Q6 was the Question and D$5 was the "Y" or "N" answere I was looking to count. I used in cell drop-downs with validation lists to change those four variables. But, that's not important. Using the formula in each cell is too hard to maintain when questions are added to the audit.

Now I want to use SQL Queries to pull the counts right out of the database and populate the cells of the table.

I have a working macro to pull the count of the first cell, but I don't know VB well enought to know how to write it to populate each of the cells with the appropriate count. In order to fill the table each cell need to be populated by a query that just a little bit different from the one next to it. e.g. C6 counts "Y"s, D6 counts "N"s, C7 counts "Y"s for the next question, etc.

If you look at the table above the first "Y" of question 1 is in C6. There are perhaps 55 questions. (Most are harder than the two above

Here is the query in the macro that populates C6. I need to modify it to include the entire table without writing 330 separate queries:


Public Sub simpleQuery()

'Connection Variables
Dim dbConnection As ADODB.Connection
Dim connStr As String

'Recordset variables
Dim rsData As ADODB.Recordset
Dim sql As String
Dim Center As Range
Set Center = ActiveSheet.Range("I1")
Dim fromDate As Range
Dim toDate As Range
Set fromDate = ActiveSheet.Range("F2")
Set toDate = ActiveSheet.Range("F3")

'SQL Query
sql = "SELECT COUNT(1) AS AttendOrientation " & _
"FROM WTP " & _
"WHERE (AttendOrientation = 'Y') AND (OneStop ='" & Center & "') AND (ReviewDate BETWEEN '" & fromDate & "' AND '" & toDate & "')"

'Connection String
connStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SigmaTools;Data Source=SQLSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WFO-14433;Use Encryption for Data=False;Tag with column collation when possible=False"

Set dbConnection = New ADODB.Connection
dbConnection.ConnectionString = connStr
dbConnection.Open

Set rsData = New ADODB.Recordset

'Put the results of the Query into cell "C6"
With rsData
.ActiveConnection = dbConnection
.Open sql
If Not rsData.EOF Then
ActiveSheet.Range("C6").CopyFromRecordset rsData
End If
End With

'Cleanup
Set rsData = Nothing
Set dbConnection = Nothing

End Sub

 

[megnin]

That macro is actually in:
Private Sub Worksheet_Change(ByVal Target As Range)
rather than:
Public Sub simpleQuery()