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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1

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" <u8822@uwe> wrote in message 
news:7882d5c08253e@uwe...
>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1
> 

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

S.Clark wrote:
>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

-- 
Message posted via http://www.accessmonster.com