Creatin a Datasheet Form that looks like CrossTab Query?

  • Thread starter Thread starter Keith Christmas
  • Start date Start date
K

Keith Christmas

I have produced a crosstab query that allows me to view the data in the
desired format, but I can't update because its a crosstab.

Field One is defined as a Row Heading, Field 2 is defined as the Column
heading and field 3 is being displayed using the 'First' function. (Since
this
value is unique I could have used any of the following: Sum, Min, Max, Avg,
First, Last).

Basically I would like to produce a sub-form in datasheet format that can be
viewed in the same way (column 2 as a heading) so that I can update Field
3.

Is this possible?

For those wondering why I want to do this...

The data model is normalised and therefore can accomodate a theoretical
infinate amount of rows - that's the data model - not Access :)
In reality however, there will only ever be 8 rows (i.e. 8 elements in the
repeating group). I want the form to show the repeating group as one row to
the user, since this is how they are used to viewing this data on the manual
form. The crosstab looks great, but obviously I can't update the data from
there.

cheers,
Keith
 
I have had to do this before, but never with Aggregated values, only when I
wanted to Pivot data to make it easier for the users to handle data entry.
I have always used First function to acquire the "Value" Here is how I
did it.

1) Using your crosstab query and programmatically add all of the values to a
temp table that you previously created. My temp table just contained text
fields like Field1, Field2, .... FieldN. Text fields gave me the most
flexibility for handling different kinds of data types text, numbers, dates,
etc.

2) Create a form that is bound to the temp table. My forms have always usea
a main form with a datasheet subform as datasheets are just easier to
manage. This form will have to be programmically morphed to accommodate
varying field structures, field names, LabelNames, datatypes, etc. Thus far
I have done this two different ways. The first was to create a bunch of
textboxes on a form Text1, Text2, .... TextN (look Familiar?) and
programmically mapping, labeling, and binding them to fields in the temp
table. The second method I have used (the method I am using now) is to
programmatically create controls as I needed them on the form whilst binding
them to the temp table. This allows me to use controls other than text
boxes. My current app uses both Text and Combo Boxes, but could be made to
use other controls.

3) After you have Morphed the form and displayed it. Let your users change
any data they desire. However they may NOT add or Delete records.

4) Finally, when the user is done editing the records you are responsible to
programmatically update the underlying data structure to the new/changed
values.

The first time I did this it was two days before I had the dam thing
working. This aint for the faint of heart, but is doable.

Ron W
 
Back
Top