Editable Crosstab/Pivot Form

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

Does anyone know of a third-party tool or method that can
allow a user from Access update via a Pivot or Crosstab
form. I have a tbale with 3 fileds, 1). Code, 2). Weekly
Date, 3). Amount. The table is normalized but the user
would like to have a form with Weekly Dates across the
top, Codes down the form, and Amounts at the intersect.
From this form they would update the Amounts in the
normalized table.

Thanks

Harold
 
This is gonna be a Roll Your own deal! CrossTabs are *NEVER* updateable.

I have had to do this in the past. And it has been a gigantic PITA every
single time.

Basically the way I have handled it is to create a temp table with fields
named field1, field2, etc..

Next write the code that invokes the crosstab to populate the table.

Then build a form (I have always used a datasheet type) that is bound to
this table. In the most simple incantation the form has fields that are
hard wired to the fields in the temp table and has attached labels named
lbl1, lbl2, etc. In the OnLoad event of this form morph all of the labels
on the form to crosstab headers, and hide any unused fields. Typically I
limit the number of fields to < 50 and have code that fires a msg box
informing the user that some columns are not displayed when the crosstab
returns more columns than I have fields.

Then in the OnClose event of this form write the code that re-normalizes
your data from the temp table back to where it belongs.

Of course there are a few small details like Keys you need to write the data
back and your customer doesn't need to see, data validation issues, ect,
but these are just details. The basic gist of this is to De-Normalize -
Edit - Re-normalize. All the while you are responsible for ..... well
Everything! This also assumes that the crosstab is just rotating your data
and you are not doing any kind of Aggregation on it.

Good Luck

Ron W
 
Hi Harold,

PivotTable is a view that summarizes and analyzes data in a datasheet or
form. You can use different levels of detail or organize data by dragging
the fields and items, or by showing and hiding items in the drop-down lists
for the fields.

The changes you make to data saved with a PivotTable form apply only to
that PivotTable form; they don't affect data in the underlying table, which
means we can't modify/update the data in the table via Pivot table/form
directly. This is the Pivot table designed working that way. However, this
is a great idea for our product further enhancement; I would suggest you
submit a wish via the website:

http://register.microsoft.com/mswish/suggestion.asp




Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| Content-Class: urn:content-classes:message
| From: "Harold" <[email protected]>
| Sender: "Harold" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.forms
|
| Does anyone know of a third-party tool or method that can
| allow a user from Access update via a Pivot or Crosstab
| form. I have a tbale with 3 fileds, 1). Code, 2). Weekly
| Date, 3). Amount. The table is normalized but the user
| would like to have a form with Weekly Dates across the
| top, Codes down the form, and Amounts at the intersect.
| From this form they would update the Amounts in the
| normalized table.
|
| Thanks
|
| Harold
|
 
Back
Top