How to fake an editable crosstab in Microsoft Access

I have spent most of the last two years working primarily in Microsoft Access, and one of the issues I ran into more than once is that there is no provision for creating an interface to edit non-aggregated data in a crosstab format. Now in most cases, editing a crosstab is simply not appropriate because 99% of the time you are dealing with aggregated or non-unique values. But in certain cases, nothing else will do. Here is an example of a dataset where editing in a crosstab format makes a lot of sense:

T-shirt size and color availability:

	Red	Blue	Yellow
Small	Yes	Yes	No
Medium	Yes	Yes	Special Order
Large	No	Yes	Yes

In order to normalize the above dataset properly, you would have to do this:

Size	Color	Availability
Small	Blue	Yes
Medium	Blue	Yes
Large	Blue	Yes
Small	Red	Yes
Medium	Red	Yes
Large	Red	No
Small	Yellow	No
Medium	Yellow	Special Order
Large	Yellow	Yes

Clearly, it is much easier to both enter and view the data in the crosstabbed format. However, if you tried to write a query to retrieve the availability for a medium red shirt, you would need the normalized format to work with instead.

So the conundrum is that we want normalized data for the purposes of retrieval, but crosstab data for the purpose of user interaction.

Access will allow you to make a crosstab query out of the normalized data, but it won't let you edit or enter the data. Why? Because Microsoft Access assumes that when you create a crosstab, you are aggregating the data, and therefore each "cell" in the crosstab does not represent a single unique value. Access does not have a provision for editing non-aggregated crosstabs, so we have to fake it.

My solution is not pretty or elegant, but if you must have an editable crosstab, it will work. The basic idea is that you store the normalized data, crosstab the data when the user wants to work with it, and then renormalize it again.

Benefits of this method:

You get to edit in a crosstab format while keeping your normalized database structure.

Downside:

-Color names (or whatever you are using for the row headers) MUST be unique.

-Sizes (or whatever you are using for the column headers) MUST be ordered perfectly (i.e., ordering starts at 1, every record has an order, and there are no breaks in the sequence).

-You cannot have more than 255 records in the table that you are using for the column headers. Access crosstabs are limited to 256 columns of data, and you need to reserve one for the row headers. (You can overcome this limitation by having one form for the first 255 columns, another form for the next 255 columns, and so on, and then have "next" and "previous" buttons that toggle the visibility of the forms).

-BIG downside: There is a long wait time to load/unload form for large data sets (you can overcome this limitation by buying a ridiculously expensive computer with multiple gigabytes of RAM, or by breathing deeply and practicing patience).

Ok, so this is how you do it:

Step 1: Make a table named tblSizes with these fields: SizeID (Autonumber, primary key), Size (Text), SizeOrder (Long). Make a second table named tblColors with these fields: ColorID (Autonumber, primary key), Color (Text), ColorOrder(Long). (Note: You need separate ordering fields because you cannot assume that the autonumbers will be continuous and sequential, what happens when you delete small and add 2X?). Put some data in each table.

Step 2: Create the normalized data set that you eventually will work with in a crosstabbed format by writing a query that contains all possible combinations of the colors and sizes you entered:

SELECT CLng([ColorID]) AS Color, CLng([SizeID]) AS Size, "Y" AS Available INTO tblAvailable FROM tblColors, tblSizes;

Now we have a normalized data set that includes every possible combination. Every possible combination is also set to "Y" (Yes) for available by default.

Step 3: Create a new crosstab query and save it as qryCrosstab. The crosstab should use the SizeOrder field for the column headers, the Color field for the row headers, and the FirstOf Availability field for the Value.

TRANSFORM First(tblColorSize.Available) AS FirstOfAvailable
SELECT tblColors.Color
FROM (tblColorSize INNER JOIN tblColors ON tblColorSize.Color = tblColors.ColorID) INNER JOIN tblSizes ON tblColorSize.Size = tblSizes.SizeID
GROUP BY tblColors.Color
PIVOT tblSizes.SizeOrder;

Step 4: Create a new module called CrosstabMadness. Create a procedure called CrosstabAvailability. This procedure will write your crosstab query to a temp table:

Public Sub CrosstabAvailability()
'This procedure writes the results of the crosstab query to a temp table
On Error GoTo EH
'delete previous temp table if it exists
If Nz(DCount("[Name]", "MSysObjects", "[Name]='tblCrosstab'"), 0) <> 0 Then
DoCmd.DeleteObject acTable, "tblCrosstab"
End If

'create crosstab
'NOTE:This only works with a maximum of 255 records in the tblSize table.
CurrentDb.Execute "SELECT qryCrosstab.* INTO tblCrosstab FROM qryCrosstab;", dbFailOnError + dbSeeChanges

Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Go ahead and run the CrosstabAvailability procedure to make sure it works, and also to create tblCrosstab.

Step 5: Create a new form named frmShowColorSize with a record source of:

SELECT tblCrosstab.* FROM tblCrosstab;

View the field list for your form. The list should say Color, 1, 2, 3, etc. Drag all of the fields onto the form as text controls. The text controls should now be named after the data sources they are bound to, i.e., the control that displays the value of field 1 is named 1. Name the associated labels Label1, Label2, and so on to match. Don't worry about the label captions - they will be loaded at runtime. On the Color control, go to properties and set it to Locked under the data tab (you don't want users accidentally editing the color names).

Figure out the maximum number of sizes you will ever need (i.e., maybe you have 4 now, but you might have up to 10 in the future). Add additional text controls for as placeholders (i.e., 5, 6, 7, etc.). Bind and name the placeholder controls and labels to match the other controls even though those fields don't exist in your current data set. So the control that displays the value of field 5 should be named 5, and it should be associated with a label named Label5. Keep in mind, it is easier to add more now than to go back and add them later. If you have too many to do this by hand, then write a procedure with an incrementing integer that loops through each control on the form and names it appropriately.

Step 6: Go back to your module, and write two more procedures. The LoadCaptions procedure will update the captions on the form at runtime:

Public Sub LoadCaptions()
'this procedure loads the captions that will show up as the column headers
Dim i As Integer
Dim rst As DAO.Recordset
On Error GoTo EH
'get a recordset of all the sizes, make sure they are in order
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSizes Order By SizeOrder;")
rst.MoveFirst
For i = 1 To 8 'or however many text controls you placed on your form
If rst.EOF = False Then 'load the value into the caption
Forms!frmShowColorSize("Label" & i).Caption = rst.Fields("Size")
rst.MoveNext
Else 'no more to load, set caption to blank
Forms!frmShowColorSize.Form("Label" & i).Caption = ""
End If
Next
rst.Close
Set rst = Nothing
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

The AdjustColumnWidths will format the columns appropriately at runtime. Since we have created more controls than we are going to use, we need to hide the ones that will not be populated and we need to adjust the width of the controls to fit the data they contain.

Public Sub AdjustColumnWidths()
'this procedure adjusts the column widths to fit their current contents
Dim ctl As Control
Dim lCount As Long
On Error GoTo EH
lCount = Nz(DCount("[SizeID]", "tblSizes"), 0)
'this is how many columns are populated with data
For Each ctl In Forms!frmShowColorSize.Controls
   If ctl.ControlType = acTextBox Then
           If IsNumeric(ctl.Name) Then
           'if the name of this control is greater
           'than the number of populated controls, hide this column
                   If CInt(ctl.Name) > lCount Then
                       ctl.ColumnHidden = True
                   Else 'autofit the populated columns
                       ctl.ColumnHidden = False
                       ctl.ColumnWidth = -2
                   End If
           End If
    End If
Next
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Step 7: Write one more procedure to tie it all together and load the form.

Public Sub LoadCrosstabForm()

   On Error GoTo EH
'this procedure crosstabs the data, opens the form, and formats it correctly.
CrosstabAvailability
DoCmd.OpenForm "frmShowColorSize", acFormDS
LoadCaptions
AdjustColumnWidths
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Call this procedure from the on click event from a button on another form.

Click the button. Your form should now open and display your crosstab data. But we aren't done yet. What happens if you edit the data, and you want to save the changes?

Step 8: Create a new table named tblNormalize with three fields: ColorName (text), SizeOrder (long), and Availability (text).

Step 9: Go back into your module and create a new procedure called NormalizeIt:

Public Sub NormalizeIt()
Dim rst As DAO.Recordset
On Error GoTo EH

'delete existing data from temp table
CurrentDb.Execute "DELETE * FROM tblNormalize;", dbFailOnError + dbSeeChanges

'get a recordset of the column headers
Set rst = CurrentDb.OpenRecordset("SELECT SizeOrder FROM tblSizes;")
rst.MoveFirst
Do While rst.EOF = False
' "un" crosstab the data from tblCrosstab into tblNormalize
CurrentDb.Execute "INSERT INTO tblNormalize ( ColorName, SizeOrder, Availability )" & Chr(10) & _
"SELECT Color, " & rst.Fields("SizeOrder") & ", [" & rst.Fields("SizeOrder") & "]" & Chr(10) & _
"FROM tblCrosstab;", dbFailOnError + dbSeeChanges
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'update the original normalized dataset based on edited dataset
CurrentDb.Execute "UPDATE ((tblNormalize INNER JOIN tblSizes ON tblNormalize.SizeOrder = tblSizes.SizeOrder) " & _
"INNER JOIN tblColors ON tblNormalize.ColorName = tblColors.Color) " & _
"INNER JOIN tblColorSize ON (tblColorSize.Size = tblSizes.SizeID) AND " & _
"(tblColors.ColorID = tblColorSize.Color) " & _
"SET tblColorSize.Available = [Availability];", dbFailOnError + dbSeeChanges

Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Step 10: Now add a call to the NormalizeIt procedure in the close event of frmShowColorSize.

That's it - you should now be able to open your form, view the data in the crosstab format, edit the data, and have the changes saved.

You can download the sample project (email me, I will send it to you). (This is done in Microsoft Access 2003, and requires a reference to DAO 3.6).

This is a rudimentary implementation of a project, meant to show the basic technique. If you were to actually use this method, at a minimum, you would also need to:

1. Implement error prevention/handling

2. Create an interface for adding/deleting/editing the available colors and sizes.

3. Enforce the ordering sequences of the colors and sizes (i.e., every color and size must be ordered, and the order must be continuous without any breaks in the sequence.)

4. Enforce the maximum number of sizes (i.e., you can't add more sizes than you created text controls for on your crosstab form).

5. Enforce uniqueness on color and size names.

6. Create a procedure to update tblColorSize whenever records are added or deleted in tblColor and tblSize.

7. Validate user input on the crosstab form (i.e., they have to enter Y, N, or S, etc.).

You are free to use this project and/or any of the code in it however you want. I would appreciate your feedback on whether you found this project useful, and any suggestions or improvements you have.