PivotTable?

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Is there a way to have data in a form which looks like a
Pivot Table. e.g. have studentName field as column
heading, have question field as row heading, then have
answer field as data. I can see this data in Pivot Table
view but cannot amend it. Can Access create forms like
this?

Alex.
 
Alex

Yes! I have had to do this *many* times. If you can live with a read only
datasheet you need to:

Create a form with a bunch of text boxes named something like text1, text2,
text3,... The text boxes will need attached labels named label1, label2,
label3,... Create as many text boxes as you think you will *EVER* need.

When you need the form open it an set its Record source to the CrossTab
query or a sql statement.

In the OnLoad event of the form you will need to run some code that looks at
your forms' recordset and sets the all of the TextBoxes Source and the Label
Captions to the Field names of the reordset. It will also have to hide any
textboxes that are not used. The code might look like

Set rst = Me.Form.RecordsetClone
For i = 0 To rst.Fields.Count - 1
If i < 30 Then
Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
Me("Label" & i + 1).Caption = rst.Fields(i).Name
Me("Text" & i + 1).ColumnHidden = False
Me("Text" & i + 1).ColumnWidth = 1500
End If
Next
For i = i + 1 To 30
Me("Text" & i).ColumnHidden = True
Next
Set rst = Nothing

In this case I planed for a max of thirty Fields.

However if you want the fields to be updateable then you will need to Insert
the results of your crosstab into a temp table, and point your form to the
temp table, update the field and label names as above.

The really Nasty part is updating your data with any fields that changed
during this edit process. You'll have to write some code that does this.
Obviously the code might be rather complex and slow executing depending on
the source for the Crosstab. Typically I add a Dirty field to the temp
table that I set true whenever any field on the row was edited . That way
if the user updated only one field I have to update only one row of fields.
This not for the feint of heart.

Ron W
 
Alex

When the data has to be updateable you MUST use TempTables. CrossTab
queries are never updateable.

I typically use two temp tables, one to hold the data, and another to
describe it.

In the data table for your data I'd create something like:
CREATE TABLE [tblTemp] (
[TempID] [int] IDENTITY (1, 1) NOT NULL ,
[Dirty] [bit] NOT NULL CONSTRAINT [DF_tblTemp_Dirty] DEFAULT (0),
[CurUserID] [int] NOT NULL ,
[StudentID] [int] NULL ,
[StudentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TempN....] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Where you have as many Temp columns as you think you'll EVER need. If the
database is single user you do not need the CurUserID field.

For the table that describes the data you'll need something like this:

CREATE TABLE [tblDataStruct] (
[ColumnDataID] [int] IDENTITY (1, 1) NOT NULL ,
[CurUserID] [int] NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Where the Field Name would contain the Name of the Field that holds the data
(Temp1, Temp2, Etc.) and Display Name would be the text that you displayed
in the attached label for the control on your form for the column
(Question1, Question2, etc.).

You may also have to add a few more fields in tblDataStruct to make it
easier for you to mung the edited data back into your normalized data
structure. In my experience the toughest part of this process is
re-normalizing the edited data. You'll be writing code the opens and loops
through a recordset of the temp data, looking for the record from which it
came and updating that record or creating a new record if there was no
original record.

The dirty column will allow this routine to determine if any change was made
on the row and allow you to skip the row when not set. Of course your form
will be responsible to set this field on the afterupdate event. Make sure
that the user can not add new records or delete existing records and you
should be good to go.

It's not really that bad, but there are a lot of steps, and therefore places
for things to go horribly wrong.

Ron W
 
Back
Top