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:
ALTER TABLE tbl_Data_Xtab ADD PRIMARY KEY (DATA_KEY);
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, _
adLockOptimistic
'Set to true if the recordset opens
fRstOpened = True
If Not .EOF Then
.MoveFirst
'Loop through each field from the picklist
Do Until .EOF
strRunSQL = strRunSQL & _
.Fields("DESIGN_FIELD") & "], ["
.MoveNext
Loop
End If
.Close
'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
strRunSQL = strRunSQL & " INTO DESTINATION_TABLE FROM SOURCE_TABLE;"
'Run the SQL statement
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strRunSQL
cmd.Execute
Set cmd = Nothing
Exit_Create_NewTable:
'If recordset still open when exiting, close it
If fRstOpened Then
rst.Close
End If
'Kill all objects
Set rst = Nothing
Set cmd = Nothing
Exit Sub
Err_Create_NewTable:
'If result table doesn't exist to delete
If Err.Number = 7874 Then Resume Next
'OTHER ERROR HANDLING HERE
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") & ", "
Else
strRunSQL = strRunSQL & .Fields("DESIGN_FIELD") & ", "
End If
.MoveNext
Loop
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