Editable crosstab querie

[Albert Wammes]

Is it possible to create form containing an editable crosstab querie?

The only way I can think of is to create a temporary table where the results of the crosstab querie are stored, then presented in a form for edit purposes and when closing the form 'translating' the temporary table back into the original one.

(simplified) Example:

Table:
- id, week, number
1 1 a
1 2 b
1 3 c
2 1 d
2 3 e

Crosstab:
- id, week1, week2, week3
1 a b c
2 d _ e

In this crosstab the a,b,c,d,e and _ should be editable.

In the current solution you can select a row in the crosstab form in order to open a form presenting the original table (filtered on id), but direct editing is highly appreciated.


Albert

[Rabbit]

You can't have a directly editable crosstab. By design, crosstabs are meant to summarize data.

[nico5038]

Hmm, in a case like this I sometimes "drop" the normalization rules and use for every week a fixed field in my table. (So Week1 to Week53)
This will allow easy updates, but will require additional tricks to use the data in other ways...

Nic;o)

[Albert Wammes]

This solution came to mind, but this will involve a lot of calculations.

For your information, it involves an activity week planning where each activity can take place in different weeks and can last for over a year. To denormalize the weeknumbers I probably will have to use a relative week number to keep the table correct over the years (another solution is to define the week from today up to the next 5 years or so - implying over 250 colums, but this is not very flexible).
Other functionalities in my program imply adding up the activities hours for a given week. This will then imply a difficult algorithm because you have to use different weeknumbers indicating the same week.

Perhaps using a crosstab query to fill a temporary table, having the format of the results of the query and edit in this table is to only feasible solution. Of course then I will need an algorithm to update the original activity table with the results from the temporary one. But this is a less complicated algorithm and futhermore this is to be programmed in one place.

I will keep this question open for the next few days, to give people the chance to come up with other ideas ;-) If nothing is coming up, I will try the temporary variant.

Albert

[FishVal]

Hi, Albert.

You may take look at this thread - Crosstab Behaviour required for Data Input

If you want to get samples mentioned in the thread, then send me PM with your e-mail.

[Albert Wammes]

FishVal,

indeed this thread reflects my problem.

I''ve send you a PM for the examples.

Albert

With FishVal's answer this question is resolved satisfactory.
Looking forward to the examples.