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!

 

[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.

 

[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/AlbertKallal/Articles/Grid.htm

 

[S P Arif Sahari Wibowo]

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.

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!