Vertical Data - CrossTab Form

[Ron Weiner]

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

 

[rob]

Is it possible to use "vertically" stored data on a CrossTab style
continuous form ?

Let's say the data is stored in a table with the following structure...
Customer
Year
Week
Quantity

The form (into which changes to this data must be made) needs to display
"horizontally" (similar to a spreadsheet)...

Week1 Week2 Week3.....
Customer1
Customer2

Would the best way to deal with this be to convert the data into a temporary
"cross-tab" style table, then when editing has finished, convert it back
into the "vertical" table, or is there another way to deal with this ?

If so, is there a book or website that describes how ?

Thanks !

 

[rob]

Thanks for the response Ron...

Hmmm... the data must be "updateable".... my origininal plan was to convert
the data into a cross-tab style table for editing. I would also store the
beginning Week and Year in order to have a reference when converting it back
(i.e., column1 = Week7, Year 2004)... which way do you think is better ?

Thanks,
Rob

 

[Ron Weiner]

Rob

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 ,
[CustID] [int] NULL ,
[CustName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [int] NULL ,
[Temp2] [int] NULL ,
[Temp3] [int] NULL ,
[TempN....] [int] 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 (week1,
week2, or Week of 1/1/2004).

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 flag 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

 

[rob]

Thanks for your help Ron...

Rob

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 ,
[CustID] [int] NULL ,
[CustName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [int] NULL ,
[Temp2] [int] NULL ,
[Temp3] [int] NULL ,
[TempN....] [int] 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 (week1,
week2, or Week of 1/1/2004).

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 flag 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

Thanks for the response Ron...

Hmmm... the data must be "updateable".... my origininal plan was to convert
the data into a cross-tab style table for editing. I would also store the
beginning Week and Year in order to have a reference when converting it back
(i.e., column1 = Week7, Year 2004)... which way do you think is better ?

Thanks,
Rob


looks hide
any

depending

on

this

?