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

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Active Member
Joined
Feb 27, 2002
Messages
340
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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?
 

Forum statistics

Threads
1,176,262
Messages
5,902,216
Members
434,950
Latest member
hughsiepops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top