updateable rows to columns? (without aggregate)

[Guest]

any comments greatly appreciated. vba/subforms, etc... any method to
get end result. i'm building a scheduling system and i need to show a
'grid' of employee names as the first column heading followed by a
dynamic number of work weeks (dates). dynamic meaning the number of
work week columns to show isn't predefined. employees will enter how
many hours they are planning to work that week. i was hoping to do it
with a query or other bound object so i don't need a submit button,
etc. i'm simplifying a bit but now my table is holding the data like:

id | name | weekbegindate | hours
1 | Tom | 8/14/06 | 32
1 | Tom | 8/21/06 | 40
3 | Fred | 8/14/06 | 40
3 | Fred | 8/21/06 | 40
etc...

i'd like the interface to ultimately look like
Name | 8-14-06 | 8-21-06 | 8-28-06 ...
Tom | 32 | 40
Fred | 40 | 40

ok, so i guess i lied a bit about not aggregated. it is grouping by the
person's id. but the data to be updated isn't manipulated. if there is
a 2 part key (id & date) it should still know what row to update right?
there may be 30 - 50 date columns so a union query would be really
messy. i'm hoping there's a way to make an updateable query that is
easily created with some sort of translation/pivot.

thanks again.
mark

 

[Michel Walsh]

Hi,


While a crosstab can easily produce the presentation, it won't be
updateable. On the other hand, if you do it under a form, you *may* be able
to intercept the modification in the control (of the form) before submitting
the modification to the tables (with an error, since it cannot be modified).
In other words, you intercept the change in the control with an afterUpdate
event of each control and then, you have to produce code for adding a
record, or modifying the existing one, if there is one, undo the change, and
requery the form (which will requery the crosstab which, in turn, will
display your latest modif).


Another easier solution is to make a table, form the crosstab, and to use a
form based on that temporary table. On exit (of the form), you check each
field of each row and adjust the table (adding or modifying records) which
provides the crosstab in the first place.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks! i would have never thought of the intercept idea. i'm not sure
about the temp table approach. it will be in a multiuser environment. i
don't expect the same item to be updated by multiple people but i'd
like to try to keep everything as live as possible. even if it's only a
few seconds different. i appreciate your help. if it doesn't work out i
can always make something that only works with one user or one time
period at a time. they currently ms project but want to replace it.
ideally, i'd keep the look/feel similar to avoid extra training time.
thanks again.