Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
In the last post we linked the Fields Table to some code that translated the table into an SQL Select clause. Most often, that’s enough. But sometimes we need to manipulate the data before loading it into our result spreadsheet. We can do that by adding SQL functions like MID( ), ROUND( ), SUM( ), or in the case below, a simple concatenate.
In this example, I have added a column “SQL Func.”, hit Ctrl-Shift-N (to resize the named range “Fields” to include the new column), and entered a simple formula to combine the customer’s last name with the first name. The result will look like “Hatmaker, Craig”.
NOTE: Microsoft’s Access and SQL Server concatenate with the “+” (plus) sign. DB2 and Oracle use “||”. Oracle also uses CONCAT(a, b) as does MySQL.
NOTE: Some SQL functions aggregate multiple rows of data, like MAX( ), MIN( ), COUNT( ), etc. These SQL functions require a GROUP BY clause. If you use one of them, you must add the proper GROUP BY clause to your SQL Select Statement or it will fail.
Now here’s the nice part. There’s no coding this time. This feature/functionality (without the aggregating functions) was included in the code from the last post.