Exploring Your Data With Subdatasheets  

By Garry Robinson                          

Having worked with Access since the days Access 2, I am always interested in changes that will improve life for my clients.    Each new version has had many new features suited to the developer or that improved the inner workings of the software but improvements for the average Access user have been very thin on the ground.  The major exception to this trend was the Access 95 version which introduced Filter by Example and Filter by Form and also added filter capabilities to tables and queries.  

On the surface Access 2000 also seemed to have little for end users with one exception.  This was a little cross that appeared on the left hand side of some of the tables when you opened them up.   This little cross seemed really neat because when you clicked on it, it showed detail from a related table.  But I soon continued testing my  converted Access 97 databases and I basically forgot all about it.  Late in 1999, I developed a new Access 2000 database for a client and in the process discovered ways to make the cross useful for the user.  This article explains how to use this cross (which is the entry point to a subdatasheet) to help the users of your software explore their data.    Interestingly I could not find any information on the feature except that I worked out it would only appear in the primary tables of one to many relationships.   An example of drilling down using the cross is shown in figure 1.

Figure 1.  Exploring your data with subdatasheets.  Example shows drilldown to 3 levels.

Making The Most Of The Subdatasheets

When I am working on Access projects, I am always commited to producing a good data model prior to developing the forms and reports that will complete the application.   Interestingly I have had great success in smaller projects delivering an application that consists of only tables and queries.  As a result, the first version of the software that I deliver to the client consists of the following

1) A well refined data model with all the trimmings such as indexes, lookup tables, referential integrity, formats, data entry rules and field descriptions.

2) A form that basically resembles the relationship diagram with buttons to open each table in the database.   I will call this the database explorer form.

3) A form that fires off all the queries that are representative of the reports that the software is to produce.

That is it for version 1.0 as this is enough to test the data model.   After the users enter some trial data and test that the queries are producing suitable results, the data model will have a solid foundation for generating the actual application.

 The Database Explorer Form

 This brings me to the Database Explorer form and how I first realised the importance of subdatasheets.   Now Figure 2 shows the data entry system for the demonstration database that comes with this article.   Here there are 2 main tables Location and Tasks,  2 main category tables called Suburbs and Work Categories plus a few lookup tables that are used to verify that codes are being inputted correctly.  Figure 3 shows the relationship diagram and as you can see there are a lot of one to many relationships with enforced referential integrity in this small database.   All of these have subdatasheets enabled in the primary table.   This makes for a total of 10 different subdatasheets that are already enabled in this database.

When you open the Locations table using the Locations button, you will find a subdatasheet button is available to see the individual tasks for each home location in the database.  The database was a home maintenance program for a government body in which the government performed home maintenance tasks for needy people in the community.  You will find that this form is a simpler version of the relationship diagram as shown in figure 3.

Figure 2.  The data exploration form consists of buttons that open tables

Figure 3 -  The relationship diagram showing the key tables plus a number of Lookup tables.

The other thing that data exploration form offers is a button to open a special form that I use in all my systems to display only the lookup tables (all tables starting with tlkp ) .   Whilst you may have a different convention for lookup tables, this form has proved very useful as you can direct users to the form to change/add lookup values rather than going through the database container.  In Figure 4, the Language lookup table allows me to instantly find all the clients that speak English and in turn look at their outstanding jobs.  All this functionality from a tool that I setup by simply importing a tlkpViewer form into my database or by opening a table.

Figure 4.   Lookup tables offer instant data exploration capability.

Now there must be a catch and there is.   Most systems are not managed by allowing users to directly enter data into a table.    Therefore when the users have finished testing the system, you generally would delete or hide the Database Explorer Form and you would provide your interface through forms and reports.   But these subdatasheets are very useful so here is what I did with the Data Exploration form.  I open all the tables as read-only.  That’s it, a great data exploration utility in a flash that allows 10 different windows into the data in the database.  I even have a Read-only argument that I can pass to my Lookup table form to tell it to only open the lookup tables in read-only mode.

 Making Your Own Relationships

As the users of this software showed a lot of interest in these subdatasheets, I decided to look into them more.  Note that at this stage, I did not know they were called subdatasheets and only knew that they appeared when there was some sort of referential integrity on a table.   So I experimented with the relationships and found that they will only occur for relationships as shown in table 1.  To find these constants I used the object browser and also viewed the MsysRelationships system table.  

Table 1 - Relationships constants and whether they will produce a subdatasheet

Now I wanted to generate my own relationship so that I could add a subdatasheet programmatically.   To do this I had to find out how to build a relationship using visual basic.  In Access 2000 it wasn't easy because none of the help on relationships showed any visual basic examples.  When I returned to Access 97, the createRelationship method of the Microsoft DAO Library finally appeared in the help results.  So the lesson there is return to Access 97 if your help may need DAO functions.   For the examples that follow, you will need to include a reference to  the DAO 3.X library in your project.

The Consolidation Query - Detail Example

As some of those who have read my previous articles would know, I have a soft spot for consolidation queries.  To further illustrate the techniques for building your own subdatasheets,  I am going to produce a query where you can drilldown on the consolidation groups to see the detail records.

Relationship Building Source Code

The visual basic that handles this code uses a combination of DAO methods and good old SQL to generate the output.   Initially I first have to make a new table as referential relationships such as Cascade updates can only occur between tables and the primary table must have a unique key.  The 2 SQL statements that I use are a Make Table query and then a Create Index query with a Primary Key clause. 

' Run a make table consolidation query

sqlStr = "SELECT ProductName," & _
 " Sum(Sales) AS SumSales" & _
 " INTO TotalProducts FROM zWorldDemo " & _
 " GROUP BY ProductName;"

DoCmd.RunSQL sqlStr

' Now create a relationship between the consolidation
' table and the original data by first creating a
' primary index on the new table and then creating
' the relationship

sqlStr = "CREATE UNIQUE INDEX " & _
" MyIndex ON TotalProducts (productName) With Primary"
DoCmd.RunSQL sqlStr

Now I to generate a Cascade Delete relationship between the new table called TotalProducts and the corresponding group by field in the detail table called "zWorldDemo".   This first involves creating a RelationObject which I have given the name  "My Relationship".  To see the different types of joins that you can use in the relationship, see the constants in Table 1.    Once you have established the relationship you will need to add a field to the new relation object and define which field is to be used in the foreign table.

Dim sqlStr As String
Dim dbsRelate As DAO.Database
Dim relNew As DAO.Relation

Set dbsRelate = CurrentDb
Set relNew = dbsRelate.CreateRelation("MyRelationship", _
   "TotalProducts", "zWorldDemo", _
   dbRelationUpdateCascade) 

With relNew
' Need to create a field in relation object prior to
' defining the name of the external field.

  .Fields.Append relNew.CreateField("ProductName")
  .Fields!productName.ForeignName = "ProductName"
  dbsRelate.Relations.Append relNew

 DoCmd.OpenTable "TotalProducts", , acReadOnly
 dbsRelate.Close

Prior to running all this code, you will need to delete the relationship and the consolidation table in case they exist in the database

On Error Resume Next
' Clean up the initial relationships in case they exist

Set dbsRelate = CurrentDb
  With dbsRelate
    .Relations.Delete "MyRelationship"
   .Close
End With 

' Remove the old consolidation output table

sqlStr = "drop table TotalProducts"
DoCmd.RunSQL sqlStr

On Error GoTo 0

The Penny Drops - All About The New Subdatasheet Property

If you already know a little about subdatasheets, you probably wonder why I went through the pain of creating a relationship to generate the subdatasheets.    Well I just came across no explanation in many hours of using Access 2000 to suggest that this was anything more than an undocumented feature.   That was until I opened the table called tlkp_Referral which  has 2 one to many relationships (see figure 3).   This brought up a wizard that asked me to define which table and field I wished to use as my subdatasheets fields.  This accident taught me that I should look for help on subdatasheets which on the whole is really quite good.

Figure 5 - Defining a join for a subdatasheet

So to add a subdatasheet manually, here are the surprising facts.  You are not required to add a relationship at all and there is no requirement to use primary keys and tables.  In summary, you can make subdatasheet join between 1 or more fields in the same way as you can define master and child fields in a subform. 

So without laboring too much over the manual techniques, here are the methods you can use.

For a table or query, open the table in data entry mode and choose the Insert Menu à now select Subdatasheet and you will get the same wizard as shown in figure 5.

Once you have a subdatasheet established, you can click on the Format menu and choose Subdatasheet from the menu.  This will give the option of removing the subdatasheet, expanding all subdatasheet rows so that you see all the detail rows and turning expand rows off again.

You can also control the maximum height that is used to display the subdatasheets by simply resizing the row manually with your mouse.   This allows you to see the first 2-3 rows rather than every row.

But the feature that I really like is that you can actually perform filters on the subdatasheets.  This means that you can really explore your data in a fashion that you used to only ever achieve with forms and subforms.  The advantage is far less code and less onscreen clutter.  

Behind The Scenes - The Subdatasheet Properties

When you use these manual commands or wizards, Access modifies the 5 new properties in the Table or Query definition.  If you open a table in design mode and click on properties as shown in figure 6, you can see the properties that  control how your subdatasheet is displayed.   Queries have exactly the same properties.

Figure 6 - The subdatasheet properties that control the display

Setting All The Properties In Visual Basic

The online help for the manual manipulation of  subdatasheets is pretty good except for the bit where it says you can control the properties in visual basic.   Then you have to search all over the place for the code to control the properties of a table and a query.   Given that it is in the DAO library, I once again found it in Access 97 help.   But you don't have to worry because here is all the code you need to set the subdatasheet properties for a query.

addOK = subDataSheetQry_FX("qrySumProducts", _
"zWorldDemo", "ProductName", "ProductName", True, 1100)

The subDataSheetQry_FX function passes all the 5 required properties to the following function which has option arguments for the expanding and height properties.   This function then calls another function to modify the 5  subdatasheet properties.  Code snippets follow.

Function subDataSheetQry_FX( _
 qryName As String, SubDSName As String, _
 LinkChild As String, LinkMaster As String, _
 Optional subDSExpand, Optional subDSHeight) As Boolean

 subDS_OK = qryPropMod_FX(qryName,  "SubdatasheetName", SubDSName)

'  Etc Etc - Now demonstrate the optional arguement

  If Not IsMissing(subDSHeight) Then
    subDS_OK = qryPropMod_FX(qryName, _
    "SubdatasheetHeight", subDSHeight)
  End If

But the really tricky code is setting the properties and the main reason I floundered is that I was caught by the dual ADO and DAO library issue.   The methods to manage properties is also supported to some extent in ADO and because I had set my reference to DAO after ADO, the property setting methods were mixed up between ADO and DAO and I received errors that simply set me off on the wrong track for a number of hours.  So if you haven't already done it, start working through your Access 97 code and adding the "DAO."  prefix to all your DAO methods and properties or you are just going to get caught like I was.

So the function that sets the Query property works by first trying to modify the existing property value. 

  Dim dbs As DAO.Database, qdf As DAO.QueryDef  Dim prp As DAO.property
  Const conPropNotFound As Integer = 3270

  Set dbs = CurrentDb
  Set qdf = dbs.QueryDefs(qryName)  

' Enable error handling.

  qdf.Properties(propertyStr) = valStr
  dbs.QueryDefs.Refresh
  qryPropMod_FX = True
  Exit Function

If the property does not exist, the error is trapped and the property is instead appended to the query properties collection.  The CreateProperty method has a second optional argument where you can set the data type of the property.   As the subdatasheet properties are a mixture of  dbText, dbInteger and dbBoolean constants, I ended up using the dbText as a common constant unless the CreateProperty Method crashed as was the case for the Expanded property.  The help says this data type is an optional argument but I am not sure when it is optional so you just have to work out what to set by trial and error.

queryPropertyMod_Fail:

  ' Check number of error that has occurred.
  If Err.Number = conPropNotFound Then  

'   The property does not exist, add it
    Select Case propertyStr
      Case "SubdatasheetExpanded"
        propType = dbBoolean
      Case Else
        propType = dbText
    End Select 

    Set prp = qdf.CreateProperty(propertyStr, propType, valStr)
    qdf.Properties.Append prp
    Resume Next

  Else

     ' If different error has occurred, display message.

    MsgBox "Error Handler ---> " & Error, vbCritical, _
    "Unknown error in qryPropMod_FX function ...  Query = " _
    & qryName & "  Property = " & propertyStr & "   value = " & valStr
    qryPropMod_FX = False

  End If

 The Consolidation Query and The Detail

 Finally has the linking of consolidation query to the detail been worth it.  Well have a look at the query that is shown in figure 7.  This shows the totals in the greyed lines.  I even put in a couple of null fields to allow the totals to align with the detail.  The detail shows the last 3 entries (reverse sorted date) which I applied by sorting the subdatasheet.   So we have ended up with a report that looks like a spreadsheet (which will please a lot of end users) that shows totals and detail.  All that for the cost of developing a query and linking using the subdatasheet properties.

Figure 7 - A consolidation query shown in grey linked to the detail records that it.

To test all the subroutines explained above, I have incorporated the subdatasheet methods into the latest version of my data mining program Graf-FX.  The findings that I can draw from that programming exercise are that

·         You can actually keep the code in an Access 97 database and the properties will be added to the queries with absolutely no effect on your application.  This is great if you are still developing in Access 97 (like Graf-FX) but would like to add this flexibility for your Access 2000 customers.

·         When you use a query with a where clause, be careful that the records that you join to are filtered as well or you may end up with all matching records rather than just the records that should be filtered.

·         You can add a subdatasheet to a crosstab/pivot query just as for a normal query.  This is pretty amazing as far as I am concerned.

Conclusion

For those of you who have read articles on ADO data shaping and used the controls such as the flex grid control,  you will realise that the subdatasheet is a very powerful end user object that doesn't require anywhere near the work of these programmable objects.   The really good thing about subdatasheets is that you can manually set up the subdatasheet in your application or turn it into an analysis tool with not much more than an openTable statement and read-only statement.

If you require a flexible reporting environment, you can either add a relationship programmatically to your application or you can manipulate the subdatasheet properties using the functions that I have provided.    This will give your users some wonderful tools to explore their data with. 

As for my projects, when I complete the data modelling phase of my projects, I will convert my Data Exploration form with its OpenTable commands and Lookup table viewer into a data exploration interface for the users by simply opening all the tables in readonly mode.   Now I will not have to throw away that useful form.

So though the setting up of subdatasheets will generally have to be done by developers or power users, this new tool is a real bonus to end users and deserves 5 out 5 stars.   I only wish the extensions to tables and queries had been more obvious to me.  I also wish that I had read Mike Gunderoloy's article on Subdatasheets which was printed in Smart Access in November, 1999.  Luckily I can claim distracted "father to be" syndrome for this slip in my knowledge exploration.

Useful Further Reading

Michael Gunderloy  "Power Up with Subdatasheets"  in November 1999 and "Data shaping" in December, 1999.

Michael Kaplan and Karleen Smith "DAO verses ADO discussions "  in  August 1999

Garry Robinson "Consolidation Queries" in October of 1998

Garry Robinson  "Using Access Filters to Graph and Explore Your Databases" 

http://www.vb123.com/toolshed/99docs/drilldownfilters.htm

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies web site at  http://www.gr-fx.com/  or sign up for his Access email newsletter here.  The web site features a popular shareware data mining tool written in Access.  When Garry is not sitting at a keyboard, he can be found playing golf or shark wrestling at one of Sydney's lovelly bays.  Contact details  …  +61 2 9665 2871  

Click on the following button to jump to the next page in the document loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals