In Access can I update records from a totals query?

[Guest]

I'm using Access 2002 for a purchase order tracking database.
The 2 main tables are Purchase Orders and Purchase Order Details.
I have a query that shows all purchase orders where the total value (taken
from purchase order details) is over a set limit say £2000. These purchase
orders will require authorisation by a manager. This is simply a yes/no
field in purchase orders that will be ticked by the manager. How do I get
round the fact that it is not possible to update records because I have used
totals in the query?

Thanks
MarionT

 

[Rick Brandt]

I'm using Access 2002 for a purchase order tracking database.
The 2 main tables are Purchase Orders and Purchase Order Details.
I have a query that shows all purchase orders where the total value
(taken from purchase order details) is over a set limit say £2000.
These purchase orders will require authorisation by a manager. This
is simply a yes/no field in purchase orders that will be ticked by
the manager. How do I get round the fact that it is not possible to
update records because I have used totals in the query?

Thanks
MarionT

All usaer interaction to data shoul dbe done with forms and reports. You can
easily build a form bound to the table that requires editing and use DSum() or
subforms to display the totals.

 

[Van T. Dinh]

Do you mean you actually has a Field "[AuthorisationByManagerRequired]" in
the Table [Purchase Orders]?

This is a "calculated" value (as you described) and in this case, you
shouldn't even store this in the Table as it can lead to inconsistencies,
e.g. an [Order Details] Record is deleted and change the Total from above
2,000 to under 2,000 and the related [Purchase
Orders] Record is not updated.

A calculated value should simply be (re-)calculated whenever you need this
value.

 

[Guest]

No, the field is a yes/no field, that is designed to show that the PO has
been authorised. The idea is that the manager will tick this box as
authorisation.

MarionT

Do you mean you actually has a Field "[AuthorisationByManagerRequired]" in
the Table [Purchase Orders]?

This is a "calculated" value (as you described) and in this case, you
shouldn't even store this in the Table as it can lead to inconsistencies,
e.g. an [Order Details] Record is deleted and change the Total from above
2,000 to under 2,000 and the related [Purchase
Orders] Record is not updated.

A calculated value should simply be (re-)calculated whenever you need this
value.

--
HTH
Van T. Dinh
MVP (Access)

I'm using Access 2002 for a purchase order tracking database.
The 2 main tables are Purchase Orders and Purchase Order Details.
I have a query that shows all purchase orders where the total value (taken
from purchase order details) is over a set limit say £2000. These purchase
orders will require authorisation by a manager. This is simply a yes/no
field in purchase orders that will be ticked by the manager. How do I get
round the fact that it is not possible to update records because I have used
totals in the query?

Thanks
MarionT

 

[Guest]

How does Dsum work? I can't find any reference to it on help screens.

MarionT

 

[Rick Brandt]

How does Dsum work? I can't find any reference to it on help screens.

DSum("FieldName", "TableOrQueryName", "OptionalCriteria")

 

[Van T. Dinh]

OK. It sounds like an [Authorised] Field.

Post the SQL String of your Query.

 

[Prosiak]

OK. It sounds like an [Authorised] Field.

Access suks.. That is all. )

Mr. Prosiak

 

[Van T. Dinh]

Don't use it if you don't want to.

BTW, learn how to spell or at least how to use a spelling-checker ...

--
HTH
Van T. Dinh
MVP (Access)

OK. It sounds like an [Authorised] Field.

Access suks.. That is all. )

Mr. Prosiak

 

[Guest]

My query now includes the following:
PurchaseOrderID (from Purchase Orders table)
DSum("LineNetTotal","PurchaseOrderDetails")
This gives me the total for all purchase orders in the system, how can I get
it to give me the total for each individual purchase order?