Beyond Excel: VBA and Database Manipulation

Dec 19 2009   2:23PM GMT

The Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Fields Table

Fields Table

The day before yesterday, one of my internal customers asked for a listing of an external customer’s orders.  Orders had to be selected based on dates, various subsidiary customer accounts, order statuses, etc.  and totalled along specific lines.  Our external customer wanted us to provide this to them because their own systems professionals couldn’t, or wouldn’t.  My internal customer said he needed it quick and that meant he really, really needed it the next day.  We handed it to him within a few minutes.  My customer beamed how happy he was that we could promise these reports and deliver.  It’s a competitive advantage.  When our external customers evaluate who to procure services from, this level of customer service may be the convincing factor that tips their business our way.

Last post I talked about how to take our reporting to the next level using Excel Tables.  Today, I’m introducing that table – the “Fields” table.  At right is a thumbnail of the table.  If you click it, you can see it in its entirety (It’s too wide for this blog’s format).  It contains most of the parameters necessary to describe data elements for a data extracts.  Here are descriptions of the various columns:

Column Description
Table Name of the database table that contains the field (see “Field” column) 
Alias An abbreviation of the table name.  Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS.  This shortens the select statement (improving readability) while identifying which field comes from which table. 
Field Name of the database field/column in the table (see “Table” column)
Key Identifies fields/columns as part of the table’s unique key 
Heading Column heading for the field displayed in the worksheet. 
S.Ord. (Sort Order) Identifies which fields are sorted on first, second, third, etc.  There is no limit to the number of sort fields. 
S.Seq. (Sort Sequence) Identifies “Ascending” or “Descending” sort sequence for the column.
Freeze Identifies which field to freeze Excel’s window pane on.  This is usefull for keeping column heading and key values displayed at all times, no matter which cell you’re in. 
Sel.Func. (Selection Clause Function) Identifies an SQL formula to use on the field.  Use a “?” to identify where the Field appears in the formula. 
Hide Use “H” to hide the column completely.  Use “C” to “conditionally” grey out repetitive values.  This is useful for extracts that contain many records for the same key value.  Duplicate key values can be greyed out leaving the first value in the list to stand out. 
Width Restricts a column to a specific width.  This is useful for improving readibility when long text fields are used. 
Format For numeric fields you can specify any numeric format understood by Excel.  For character fields you can specify alignment: L = Left, R=Right, C=Centered, W=Wrap Text.
Default For reports, this would be better labeled as “Excel Formula”.  Use this to embed calculated columns into your result. 
Input Not used for reports.  For update spreadsheets this identifies the conditions under which this field may be changed: A=Adding records, C=Changing existing records or adding records, P=By program control only (users are locked from making any changes)
Required Not used for reports.  Identifies fields that cannot be left blank.  A common synonym is “mandatory.” 
V.Type (Validation Type) Not used for reports.  Identifies rules to apply to insure values entered are valid.  
V.Tbl (Validation Table) Not used for reports.  Identifies an Excel or database table to use to validate values. 
Upd. Func. (Update Function) Not used for reports.  Identifies an SQL function to use when updating values in tables
Notes Use this the same way you would use program comments. 

I put this table in a worksheet called “Tables”.   This table is a powerful RAD (Rapid Application Development) type tool.  Once I get the basics of the report up, I can then bring in my customer who will almost always want to change the output based on either something they didn’t communicate, or based on a realization they had when they saw the results of what they asked for.  I no longer dread those moments.  I actually look forward to them.  Because while we’re talking, I see the excitement on their faces as within seconds their desires are realized – simply by adding or adjusting a row in this table.  I can easily add columns to their results by adding rows to this table – without changing my code.  I can change the initial sort to anything they want – without changing my code.   I can change column headings, add formats, and even add calculated columns – all without changing any of my code – and all within a matter of seconds.  My customers are thrilled.

In the next post we will start adding code to take advantage of this table in order to generate much of our SQL Select statement for us.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: