Unfortunately, this wasn't really one of them!

I tried all kinds of tricks to make this work, and eventually broke down and rolled my own solution. I've now used that very code so often that I think others might be able to benefit from the code the way I have.

Project scope: Counting how many fields have a value

There is this database somewhere and for each field in the database I needed to count how many of records had a non-null, non-blank value. Simple, right?

How to do it using Excel's SQL Query engine

From my blank workbook, hit Data-->Get External Data-->...

From Other Data Sources-->From Microsoft Query...

Tell it From an MS Access Database

Point at your MDB file, point at the table (tblClients in this case), and pick a couple of fields. In this case, I'm curious about how many people gave me a contact email by filling in the ContEmail field. Note: I'm not validating the email address, just curious how many people filled in the field.

So here, lets just count how many non-nulls came in.

Hit Next, Next, then I'm going to look at the query in MS Query:

Here are the sample results opened in MS Query, except I hid the actual email addresses:

And what the SQL Query looks like by hitting the SQL button:

All pretty simple, right? So lets get the count by changing the query. Change the field name to instead count(field name):

and I get a result of 67:

And when I hit File->Return Data to Microsoft Excel:

I get a nice table with one value. This works well for a couple of values - but I had 600 some-odd fields spread across 8 data tables and I wanted to do this for every single one of those fields. Now there are all kinds of ways to refresh this query, and I even wrote some VBA that will auto-refresh every query in a workbook, but this was before that pretty little "Refresh All" button existed.

So my first thought was copy / paste and edit the query to point to a different field, like ContName (Contact Name)... Highlight->Copy->Paste->Right click->Table->Edit Query, then change the query to count ContName:

Ok, so I've got 67 contact emails and 74 contact names. I tried, but found no way to reach into that SQL query and automate changing the SQL statement. Plan B was to have the query engine read the query from a cell in the table, which was also a dead-end.

I needed this same functionality for another project a few years ago, built my own tool that does the queries, and have been using and enhancing that tool ever since. What you are about to see is a very simple application of this tool.

Here is what I really wanted to happen:

See the field and table names? I want to automatically populate the next column with counts from a database.

With those two bits of data, I can concatenate up a SQL statement that will give me the results like this:

I could have counted non-nulls, but there were some spaces in some fields - so I ended up with the query you see above and listed here: select count(ID) from tblClients WHERE LEN(TRIM(ID)) > 0. That SQL code is created by a few simple string concatenation operations in Excel, so in this case I'm using Excel to write my SQL queries for me! Way easier than doing that for 600 fields.

This formula can be copied down all 600 some-odd rows and just like that I've got all my queries written and ready to execute. The Excel formula automatically builds the correct SQL statements. What I needed was code that would seek out all those queries and put the results somewhere else in the Excel spreadsheet.

The user sets up as many queries as you need in your spreadsheet. I picked the prefix "sql:" but that is a configurable parameter. The code goes through all the worksheets and all the rows / columns you tell it, and it goes around looking for that prefix. Wherever that value is found, the tool runs the query and takes the results and puts it back into the worksheet at an (x,y) offset from where the query exists. The whole function runs from a macro and when it is done all the numbers in your spreadsheet are updated!

So here are some sample results:

And here are some results from other tables, and again - all these queries were auto-generated with some very simple Excel concatenations which took all of 5 minutes to put together.

The tools has not been commercially released yet, but if you are interested in this functionality please contact me directly and we can discuss licensing the tool for your specific purposes. Automating work like this saves many hours, and every year after that I've used the same functionality just adds to the savings!

If you found this helpful or not, please send me a brief email -- one line will more than do. If I saved you a bunch of time (and thus $$), and you wanted to show appreciation, sending a little love via PayPal or an Amazon gift card is also very much appreciated!

(Experiment: October 2013)

I'm playing with integrating Facebook's like button - if you liked this article and have a facebook account, please show some love and hit the button below. Thanks!

I can be reached at:
das (at-sign) dascomputerconsultants (dot) com

Thanks!
 

David Soussan

(C) 2011 DAS Computer Consultants, LTD.  All Rights Reserved.