You are here:

Using MS Access/Use crosstab to enter data



I think you are right saying: "It would be helpful if you explain. I might be able to provide a better alternative if I understand the situation". Although it is a long explanation I would dare to offer it :-) below
Imaging you have to use crosstab as a data entry form (you enter quantities). For example:
Color    I   Small   Medium   Large
White    I      10     24       0
Blue     I       5     11       8
Green    I       0     12       7

Note: Numbers stand for qties of each color/size combination (in code below it is “Available”)

The approach is to fake a crosstab. This is briefly how it goes:

First, the following tables are created: tblSizes, tblColors and tblColorSize for interaction (all Color-Size combinations)
Then a crosstab query qryCrosstab is created:
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.SizeID;
Obviously, the query is not editable.

Then a table that stores the results from crosstab is created:
Public Sub CrosstabAvailability()
'This procedure writes the results of qryCrosstab to a temp table tblCrosstab
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 tblCrosstab
CurrentDb.Execute "SELECT qryCrosstab.* INTO tblCrosstab FROM qryCrosstab;", dbFailOnError + dbSeeChanges
Exit Sub
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Then, normalization goes:
Public Sub NormalizeIt()
Dim rst As DAO.Recordset
On Error GoTo EH

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

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

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

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

This idea is available on-line at:

In my case the historical data must be stored. So I created an Append query that adds the normalization results into another table for further reporting. This is all fine so far.

My challenge is that I have to link and store all crosstab combinations and qties with username who modifies the data (StaffID – dropdown coming from tblStaff) and the date (WeekStart). What I have done does not work but here it is:
The original entry form with record source of tblCrosstab now becomes a subform. On the main form I added 2 fields: StaffID and WeekStart with the same record source tblCrosstab (I have added StaffID and WeekStart to both tables tblColorSize and tblCrosstab).

I have modified NormilizeIt function to include StaffID and WeekStart. The result is that StaffID (Value3A) and WeekStart is only added to the very first record (Color-Size-Availability) but not all combinations. I need it to be attached to all Color-Size-Available combinations for further reporting.

ANSWER: OK, the data should be stored in a table like so:


So you have one record for each combination of Color and Size.

If you need to enter data in a grid, then either use a temp table or an unbound form. Once the data is entered, you append the records to a table as shown.

Use a Temp table if you need to set a default value as discussed on my other response.

[an error occurred while processing this directive]---------- FOLLOW-UP ----------

QUESTION: I think I was not clear enough. What you said is already done. I have implemented all that I'd listed above. It works but not exactly the way I want. At the end, after normalization, this is what I have (see below). I need the same StaffID and WeekStart be across all combinations. Next week it would be other qties in Available, new StaffID and WeekStart. If it is too much to ask then sorry to bother and thank you anyway.

Color   Size   Available  StaffID   WeekStart
White   Small     10        2345   Dec.6, 2015
White   Medium    24
White   Large      0
Blue    Small      5
Blue    Medium    11
Blue    Large      8
Green   Small      0
Green   Medium    12
Green   Large      7

ANSWER: OK, I recall answering this a while back.

The problem is in your SQL statement here:

  CurrentDb.Execute "INSERT INTO tblNormalize ( ColorID, SizeID, Availability )" & Chr(10) & _
  "SELECT Color, " & rst.Fields("SizeID") & ", [" & rst.Fields("SizeID") & "]" & Chr(10) & _
  "FROM tblCrosstab;", dbFailOnError + dbSeeChanges

I don't see how this can work. I think you have something much more complex then it needs to be.

I would do it this way:

1) Create tblCrosstab like so:
CrosstabID (PK Autonumber)

2) Build a form based on tblCrosstab. On that form have 2 unbound controls for StaffID and WeekStart. Have a button on that form to repopulate the table. The code behind that button would look like this:

Dim strSQL As String
CurrentDB.Execute "DELETE * FROM tblCrosstab;"
strSQL = "INSERT INTO tblCrosstab (Color, StaffID Weekstart) " & _
        "SELECT Color, " & Me.txtStaffID & ", #" & Me.txtWeekStart & "# " & _
        "FROM tblColors;"
CurrentDB.Execute strSQL

That code will place a row for each color in tblColors and also fill in the StaffID and Week Start data they you entered into the unbound controls.

3) Your Normalizing code would then look like this:

Dim strSQL As String

strSQL = "INSERT INTO tblNormalized (Color, Size, Quantity, StaffID, WeekStarting) " & _
        "SELECT Color, 'Small' As Size, Small, StaffID, WeekStarting " & _
        "FROM tblCrosstab;"
CurrentDB.Execute strSQL

Repeat the strSQL and CurrentDB.Execute statements for each Size column replacing what goes in the Size field and what field to pull from for the Quantity.

---------- FOLLOW-UP ----------


Sorry but it did not work at all. I am thinking about an easier way to accomplish my goal. Is there a VBA code that will go around the loop checking cell values in one column and assign the same value for all Null cells as soon as the value is found in one of them? Like below:

This is what I have now (in Column2 there is somewhere one non-Null value):
Column1  Column2
Value1    Null
Value2    Null
Value3    ValueA
Value4    Null

This is what I need (all Null values are filled with the identified non-Null value):
Column1  Column2
Value1    ValueA
Value2    ValueA
Value3    ValueA
Value4    ValueA

Sure, You can loop through all the controls on a form. Something like:

Dim ctl As Control

For each ctl in Me.controls

    assign your values to the table

Next ctl

Frankly, I think the temp table would be a better idea. Not sure why it didn't work, it should have.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.

[an error occurred while processing this directive]