Basic How To Query SQL (or named range) data to a Cell

[megnin]

I have a report that gets its data from a large MS SQL table of questionaire results.

The columns of the table are, say: QuestionID, QuestionText, Part01, Part02, ReviewerName.
Part01 can be "Yes" or "No", Part02 can be "Yes" or "No" and so on for each Question.
ReviewerName is a person's last name.

The report shows the count of "Yes" answers and "No" answers for each Part of each Question and may look like:

Question | P01_Yes | P01_No | P02_Yes | P02_No | ReviewerName | Etc...
Q1 | 95 | 5 | 96 | 4 | Jones |
Q2 | 94 | 6 | 98 | 2 | Smith |

An example of the query for just one cell may be:
SELECT COUNT(1)
FROM MyTable
WHERE (Part01='Yes') AND (ReviewerName='Jones')

Let's say that one returns the "95" Yeses above. The next one to return the "5" Nos may be:
SELECT COUNT(1)
FROM MyTable
WHERE (Part01='No') AND (ReviewerName='Jones')

How would I do that using Excel 2007 (worksheets must run on Excel 2003 though)?

Thanks in advance.

 

[Fazza]

Hi megnin.

I don't understand the approach, nor the specific question. Also, if relevant, I don't know if the source data is normalised.

"How do I do that?" leaves me unsure where to start.

Maybe start with some sample data and then a simple description of the objective. What exactly do you want to do?

Regards, Fazza

 

[megnin]

SQLTable:

Col1 | Col2 | Col3
Q1 | Yes | Jones
Q2 | Yes | Smith
Q3 | No | Jones
Q4 | No | Smith
Q5 | Yes | Jones


T-SQL query I'd like to use to get data from SQL Table to cell C6 of Excel Worksheet(Sheet1):

SELECT Count(1)
FROM SQLTable
WHERE (Col2 = 'Yes') AND (Col3 = 'Jones')

This should put "2" in cell C6

It's a report, so it's a whole table of similar calculations, so, say, in cell C7 I want to show the count of "No" for Jones:

SELECT Count(1)
FROM SQLTable
WHERE (Col2 = 'No') AND (Col3 = 'Jones')


That's the basics. I'm trying to figure out how to get the result of a T-SQL query (a count) from a MS SQL Server 2005 database into ONE CELL of a MS Excel spreadsheet. Then I need to repeat that putting a similar, but slightly different query result into many, many other cells. The report will ultimately show the results of each Question of this audit by how many times each question was answered "Yes", how many times it was "No", and also by Reviewer Name, Date, Facility and Case Manager. But, I'll worry about adding parameters for the detailed filtering later.

Thank you for your response.

 

[Fazza]

The sort of result required sounds like a cross tab report. The approach then is to generate all results in one query - just as you would in a database. Rather than individual queries for each value in each field of each record. OK?

So, whatever SQL you'd use in your database should be used in Excel and the resultant dataset loaded into a worksheet. OK?