Editable query / continuous form with aggregate fields: how?

[S P Arif Sahari Wibowo]

Hi!

I would like to make an editable continous form, where most
fields will be from table A and editable, except 1-3 fields are
a glimpse into table B and uneditable. Table A relate to table B
in one-to-many relation. I don't need to see all values in B
that relate to the particular record in A, just one value in
each field in B, preferably the last entered.

This is to ease a person that need to manually fix and encode
some of the records.

However, this means an aggregate value (last) upon B's fields
side by side with values from table A. This render the query
uneditable and hence the form also uneditable.

I tried to work on it the form itself using unbounded fields,
but since it is a continuous form, obviously each time the value
in the unbounded fields change, it change in every display of
the record. So it doesn't work.

I tried the aggregate functions, but it quite a performance hit,
and there is no DLast.

I am thinking to use a "scratch" table to contain temporary
value of the B table, but since the operator may jump to other
forms to edit value in B as well, means the scratch table will
have to be rewritten avery time that happen.

Is there anyway to make this work in Access?

Thank you!

--
(Stephan Paul) Arif Sahari Wibowo
_____ _____ _____ _____
/____ /____/ /____/ /____
_____/ / / / _____/ http://www.arifsaha.com/

[Allen Browne]

The domain aggregate functions would be the simplest to implement, though
performance will be woeful if you have 3 per record and multiple rows.
DMax() should return the highest value or the most recent date. If you want
the value from one field sorted in descending order by another field, you
could use this extended version of DLookup():
http://allenbrowne.com/ser-42.html

If your user only really needs to see the extended information for the
selected row of the continuous form, you could put the extra text boxes in
the Form Footer section, looking up the extra fields with a series of
calculated controls or even a listbox or a datasheet subform. That would
solve the performance issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"S P Arif Sahari Wibowo" <ar******@yahoo.com> wrote in message
news:Pi******************************@localhost.lo caldomain...

Hi!

I would like to make an editable continous form, where most fields will be
from table A and editable, except 1-3 fields are a glimpse into table B
and uneditable. Table A relate to table B in one-to-many relation. I don't
need to see all values in B that relate to the particular record in A,
just one value in each field in B, preferably the last entered.

This is to ease a person that need to manually fix and encode some of the
records.

However, this means an aggregate value (last) upon B's fields side by side
with values from table A. This render the query uneditable and hence the
form also uneditable.

I tried to work on it the form itself using unbounded fields, but since it
is a continuous form, obviously each time the value in the unbounded
fields change, it change in every display of the record. So it doesn't
work.

I tried the aggregate functions, but it quite a performance hit, and there
is no DLast.

I am thinking to use a "scratch" table to contain temporary value of the B
table, but since the operator may jump to other forms to edit value in B
as well, means the scratch table will have to be rewritten avery time that
happen.

[Albert D. Kallal]

I often just put up a extra form to the right of the existing form (that
means the continues form is actually a sub-form).

This arrangement allows a many to many to be displayed.

Here is some screen shots of what I mean...might give you some ideas....
http://www.members.shaw.ca/AlbertKal...icles/Grid.htm

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[S P Arif Sahari Wibowo]

On Wed, 18 Jan 2006, Allen Browne wrote:

The domain aggregate functions would be the simplest to
implement, though performance will be woeful if you have 3 per
record and multiple rows.
Yes performance doesn't seems good.
Is the performance for the domain aggregate functions depend on
the number of row in the query or the number of row displayed at
one time?
If your user only really needs to see the extended information
for the selected row of the continuous form, you could put the
extra text boxes in the Form Footer section, looking up the
extra fields with a series of calculated controls or even a
listbox or a datasheet subform.
On Wed, 18 Jan 2006, Albert D. Kallal wrote: I often just put up a extra form to the right of the existing
form (that means the continues form is actually a sub-form).
This arrangement allows a many to many to be displayed.

There two suggestions are similar, thanks, I did something like
that before. But for this particular manual audit, I need to put
up only single value from the table B for each record in A.

I guess there is no other way than make temporary tables? Let's
see whether the performance is acceptable.

Thank you!

--
(Stephan Paul) Arif Sahari Wibowo
_____ _____ _____ _____
/____ /____/ /____/ /____
_____/ / / / _____/ http://www.arifsaha.com/