update data from aggregate query

[mbr96 via AccessMonster.com]

I have a table of PO's that includes a "flag" field to indicate whether a PO
is fully paid or not. The PO table has a one to many relationship to a
payments table (multiple payments for 1 PO).

I've built an aggregate query that gives the total payments and last payment
date for the POs, but now I need to provide a user with an ability to update
the flag based on decisions from the aggregate query.

I'm building a form for this, but it I pull the flag field in the aggregate
query, it's not editable. I could use a dlookup to reference the flag field
on the form, but the whole point is to be able to edit the flag field.

Any suggestions?
MBR96

 

[S.Clark]

On a form, display the results of the queries. On the form, add a button to
allow the user to "Mark as Paid". Use an Update query to update the field in
the table.

 

[Michel Walsh]

Can you use a Form / Subform presentation?

The Form would be based only on the table, NOT on a join. The sub-form will
be based on your aggregate-based query (or computed expression). The Form
will effectively 'join' the required fields though the parent/child form
mechanic (NOT through an SQL statement). If so, you should be able to
update the table represented by the form (but not anything in the subform).


Hoping it may help,
Vanderghast, Access MVP

 

[mbr96 via AccessMonster.com]

The user needs to be able to type in various values into the flag field for
the PO, not just based on the payment data, but also other decisions in their
little brain (ahem). I'm stumbling on how to give him the aggregate query
results (query is grouped on PO, sum on payments), but also show the flag
field and be able to edit it.

I put a field in the PO table to contain the PO total paid amount, but when I
try to update it using the grouped and summed query I get the "operation must
use and updateable query".

Still confused. I could take the aggregate query and turn it into a make
table query, then use the resulting table to update the original PO table,
but that moves away from good table design (normalization).

Any other thoughts would help. Appreciate this site alot!

MBR

On a form, display the results of the queries. On the form, add a button to
allow the user to "Mark as Paid". Use an Update query to update the field in
the table.

I have a table of PO's that includes a "flag" field to indicate whether a PO
is fully paid or not. The PO table has a one to many relationship to a

[quoted text clipped - 10 lines]

Any suggestions?
MBR96

 

[KARL DEWEY]

the aggregate query results (query is grouped on PO, sum on payments), but
also show the flag field and be able to edit it.
You could create another table named PO_Flag with these fields
PO_Flag ---
PO - foreign key related to PO table - indexed, no duplicates
Flag - memo
Total_Paid_Amount - number, single

--
KARL DEWEY
Build a little - Test a little

The user needs to be able to type in various values into the flag field for
the PO, not just based on the payment data, but also other decisions in their
little brain (ahem). I'm stumbling on how to give him the aggregate query
results (query is grouped on PO, sum on payments), but also show the flag
field and be able to edit it.

I put a field in the PO table to contain the PO total paid amount, but when I
try to update it using the grouped and summed query I get the "operation must
use and updateable query".

Still confused. I could take the aggregate query and turn it into a make
table query, then use the resulting table to update the original PO table,
but that moves away from good table design (normalization).

Any other thoughts would help. Appreciate this site alot!

MBR

On a form, display the results of the queries. On the form, add a button to
allow the user to "Mark as Paid". Use an Update query to update the field in
the table.

I have a table of PO's that includes a "flag" field to indicate whether a PO
is fully paid or not. The PO table has a one to many relationship to a

[quoted text clipped - 10 lines]

Any suggestions?
MBR96