Tuesday, April 24, 2012

Table-Driven Dynamic Datasets in MS Access

I've used MS Access a lot over the years, and I generally think it's an underrated database application... you can do a lot with it, especially if you know your VBA and SQL.  VBA gives you an extra dimension of control that you can't always get with other database/reporting applications like Business Objects or MicroStrategy.  You can get pretty creative with those tools as well, and they are better at native report delivery, but it always seems to me that you can go one further with MS Access in terms of data processing and automation.

Now, Access is excellent for smaller datasets, though you can still use larger ones if you're creative enough.  With Access, just pull your data from a datamart and then do what you will with it locally.  I've created a number of tools like this for work, and in this blog I'd like to share how I create dynamic datasets in MS Access.

Dynamic Datasets

What do I mean by this, exactly?  At the core, these are crosstab/transform queries.  You take values that normally appear in rows and turn them into the columns themselves.  Same basic principle as a pivot table in MS Excel.

Like this:

The dynamic part is that the columns can change depending on the values in your data.  One month you might have one list of values, the next month you could have a few more or less.  The resulting crosstab has different columns as a result.  This kind of view is helpful for things like GL accounting, a P&L statement, or metric reporting.  You can save database space and speed up queries by storing the indexed field/value pairs in a simple table, then crosstabbing (is that a word?) them to get your reporting views.

Now, this is all fine and dandy if you just use the results of the crosstab query and maybe copy-paste-values it somewhere else.  But what if a crosstab is an intermediate step in a process?  What if you need to perform some calculations on the fields in the crosstab to create a new dataset?

Crosstab Tables

One easy way to work with the data is to make a table from the resulting crosstab.  Just create a new Make-Table query with the crosstab query as the source, then pull the * field down in the field list.  This creates a table from your crosstab.

If you need indexes or keys, use some data definition SQL to add them systematically:


What if you need to add new columns to your table?  Columns can be added with data definition SQL.  Or you can put dummy/null values in your source table, to force a column to appear in a crosstab.  Like so:

But... what if you want to control which columns appear in your final dataset?  You'd have to manually add and remove fields from the make-table query that uses your crosstab.  This can be problematic when you're not always guaranteed to have the same fields and you plan on doing more processing with the crosstab table further along in the process - queries in Access will break if fields are missing.

Designing Datasets with Tables

This is where simple tables come in.  With a table and some VBA, you can create whatever dataset you want, and control the results by merely changing a few values in a table.  Many enterprise-scale applications and databases use techniques like this to easily manage systematic processes (like daily transaction loading, etc).  For reporting purposes you use it a little bit differently.

To do this, you'll need to have four things:

1. A table with your initial set of data, in crosstab form
2. A table that holds the design of your desired crosstab results
3. A "picklist" query/view of the field names and properties you want in the results
4. A VBA function to construct the SQL that creates the resultant table

After putting this together, to change your results you merely update the data in the design table.  You won't have to modify any queries or VBA.

Step 1: Initial Dataset
This can be done using the techniques described earlier.  Just create a make-table query, use a crosstab as the source and bring all fields * into the field list.  Run this to get your initial crosstab dataset.

Step 2: The Design Table
Create a table, and have one of the fields represent the fields/columns in your crosstab.  So, perhaps you have a list of metrics or months you want to use for the columns.  The table field could be DESIGN_FIELD and for each row the value is the name of resulting column.  As long as it's field in your source it will work.  Here's an example:

Now, you can add other fields to the table that represent properties that could apply to each column.  Want to control whether a field appears in the results, like an on/off switch?  Add a Yes/No (Boolean) field called SHOW_FIELD and check the ones you want to appear.  Want to force a field to be blank?  Add a Yes/No field called KEEP_BLANK for that.  Want to custom order the fields?  Add a Number field called FIELD_ORDER and order it any way you like.

NOTE: You can do that in a crosstab anyway, by concatenating an ORDER_FIELD with the field that will be used for the column.  But to dynamically build the table using VBA, you don't need to do that.  At the end of the day, the important thing is that whatever field properties you add to the table, need to be properly handled by your "picklist" query (Part 2 below) or VBA function.  So you have to build it in somewhere.

Step 3: The Picklist Query
The VBA function you write in the next step will construct the SQL statement that will create your final result table.  In order for the VBA function to know what fields to choose, you need to give it a list.  That's where the picklist comes in.

Create a query based off the design table.  Include any relevant fields and add filtering criteria based on properties.  It's better to filter in the picklist, than in VBA, but you can use either, depending on your needs.  So, if you have a property called SHOW_FIELD to control which appear in the final results, filter the picklist query to only show the fields where SHOW_FIELD is checked:

Make sure the field name is in the picklist.  Other property fields can be shown if you need to use them in the VBA code.

NOTE: For the VBA function in the next section, you cannot use a picklist query that has a wildcard (* or %) in the filter criteria (e.g. Like "Jeff*").  For some reason, VBA will not be able to open and step through such a recordset with ADO.  If you absolutely need to use a wildcard, create a table of the picklist, then use the table in its place in the code.

Step 4: The VBA Function
Now the fun part.  Here's the VBA function you can use to build your dynamic table.  Essentially all it does is loop through your picklist query one record at a time and build the SQL statement from that, selecting the data from the raw data table.  In order to run this VBA, you'll need to make sure a reference to ADO is enabled in the VBA window (Alt+F11, then Tools --> References).

You will need to substitute the names of your objects for PICKLIST_QUERY, SOURCE_TABLE and DESTINATION_TABLE in the code.  Remember, the SOURCE_TABLE is the crosstab you made earlier.

Public Sub Create_NewTable()

On Error GoTo Err_Create_NewTable  'Error Handling

    Dim rst As ADODB.Recordset  'Holds the picklist query
    Dim cmd As ADODB.Command    'For running the SQL statement
    Dim fRstOpened As Boolean   'Tracks if the recordset is open

    Dim strRunSQL As String     'SQL statement

    fRstOpened = False

    'Delete existing design table if it exists
    DoCmd.DeleteObject acTable, "DESTINATION_TABLE"

    Set rst = New ADODB.Recordset
    'Start the SQL statement
    strRunSQL = "SELECT DATA_KEY, ["

    'Open the list of fields to add
    With rst
        .Open "SELECT * FROM PICKLIST_QUERY;", _
            CurrentProject.Connection, adOpenKeyset, _

        'Set to true if the recordset opens
        fRstOpened = True

        If Not .EOF Then


            'Loop through each field from the picklist

            Do Until .EOF
                strRunSQL = strRunSQL & _
                    .Fields("DESIGN_FIELD") & "], ["


        End If

        'Set to false now that it's closed
        fRstOpened = False

    End With
    Set rst = Nothing
    'Remove the final comma/bracket from the field list

    strRunSQL = VBA.Left(strRunSQL, VBA.Len(strRunSQL) - 3)

    'Finish the SQL statement

    'Run the SQL statement
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = strRunSQL
    Set cmd = Nothing


    'If recordset still open when exiting, close it
    If fRstOpened Then
    End If

    'Kill all objects
    Set rst = Nothing
    Set cmd = Nothing

    Exit Sub


    'If result table doesn't exist to delete
    If Err.Number = 7874 Then Resume Next


GoTo Exit_Create_NewTable

End Sub

So as a result, you get this:

This function can be reused for other datasets if you keep the same generic fieldnames in your design table.  It can further be expanded and tweaked to meet any specific field properties.  Let's say you want to force a field to be blank.  Make sure the KEEP_BLANK field is visible in the picklist query, then adjust the VBA so that when the KEEP_BLANK field is checked for a record, the property passed to the SQL statement is NULL instead of the actual field value.  Address it in the loop for the field list:

Do Until .EOF
    If .Fields("KEEP_BLANK") Then
        strRunSQL = strRunSQL & "NULL AS " & _
            .Fields("DESIGN_FIELD") & ", "
        strRunSQL = strRunSQL & .Fields("DESIGN_FIELD") & ", "
    End If


Now, this may not look all that impressive, but when you factor in 10-20 fields and things like calculations between fields, or trending between fields across multiple months, you can see how much easier it is to manage when you control the structure of your results with values in another table as opposed to manually changing queries.

NOTE: If you have trouble creating your desired SQL statement in VBA, create the query manually as a normal query in design view, then switch to SQL view.  Use that as the base in the VBA code.

No comments:

Post a Comment