Cannot edit field in a joined table-subquery query

[Nando]

Hi all! I need help creating a query that allows me to edit a field on the
grid.

I'm in query design view and I import the table ORDERS and the query
QRY_SUMOF_ORDERDETAILS as a subquery. I joined the PK of table with the FK
of subquery. The field I would like to edit is Comment, located on the
one-side of the relation. The many-side is the subquery
(QRY_SUMOF_ORDERDETAILS), which is just a query that groups and sums the
related (many-side) records of the table ORDERDETAILS. The final result of
this subquery is one record per each ORDER.

I want to be able to edit the Comment field. But I'm unable to do so, the
machine just beeps. Any help would be appreciated. Below the table layout.

ORDERS (order's header / one-side)
fields: OrderPK | Client | Comment |

ORDERDETAILS (details of orders / many-side)
fields: OrderFK | Itemname | Total

So what I want is a query that outputs:
OrderPK | Client | Comment | SumOfTotal

and being able to edit the Comment field.

 

[John Spencer]

I think you might have to use a subquery in the select clause to have
any chance of this being an updatable query.

SELECT OrderPk, Order.Client, Comment,
(SELECT SUM(Total)
FROM OrderDetails
WHERE OrderDetails.OrderFK = Orders.OrderPK)
FROM Orders


If that doesn't work take a look at using DSUM to get the value.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Hi all! I need help creating a query that allows me to edit a field on the
grid.

I'm in query design view and I import the table ORDERS and the query
QRY_SUMOF_ORDERDETAILS as a subquery. I joined the PK of table with the FK
of subquery. The field I would like to edit is Comment, located on the
one-side of the relation. The many-side is the subquery
(QRY_SUMOF_ORDERDETAILS), which is just a query that groups and sums the
related (many-side) records of the table ORDERDETAILS. The final result of
this subquery is one record per each ORDER.

I want to be able to edit the Comment field. But I'm unable to do so, the
machine just beeps. Any help would be appreciated. Below the table layout.

ORDERS (order's header / one-side)
fields: OrderPK | Client | Comment |

ORDERDETAILS (details of orders / many-side)
fields: OrderFK | Itemname | Total

So what I want is a query that outputs:
OrderPK | Client | Comment | SumOfTotal

and being able to edit the Comment field.

You can't, because no Sum query nor any query including a sum query is ever
updateable.

You can use a Form based on Orders, with a subform based on Orderdetails; you
can put a control in the subform's Footer with a control source

=Sum([Total])

and (if you wish) just not put any controls in the detail section, or you can
put a textbox on the form with a control source

=DSum("[Total]", "[OrderDetails]", "[OrderFK] = " & [OrderPK])

John W. Vinson [MVP]

 

[Nando]

I think you might have to use a subquery in the select clause to have any
chance of this being an updatable query.

SELECT OrderPk, Order.Client, Comment,
(SELECT SUM(Total)
FROM OrderDetails
WHERE OrderDetails.OrderFK = Orders.OrderPK)
FROM Orders

Thanks John, but I just tried this an it stills beep.

If that doesn't work take a look at using DSUM to get the value.

hmm...How do I use that?

 

[Nando]

You can't, because no Sum query nor any query including a sum query is
ever
updateable.

Thanks John, I kinda realized that at some point. That's why I tried to
isolate the Sum as a subquery. So I tried to trick it But you are right,
using the Sum in a query (in any way) is never updatable.

You can use a Form based on Orders, with a subform based on Orderdetails;
you
can put a control in the subform's Footer with a control source

=Sum([Total])

and (if you wish) just not put any controls in the detail section, or you
can
put a textbox on the form with a control source

=DSum("[Total]", "[OrderDetails]", "[OrderFK] = " & [OrderPK])

Hmm.. I'm not sure if I follow... Can I then make the form view as a
spreadsheet so it can look as below?
OrderPK | Client | Comment | SumOfTotal

 

[John W. Vinson]

Hmm.. I'm not sure if I follow... Can I then make the form view as a
spreadsheet so it can look as below?
OrderPK | Client | Comment | SumOfTotal

Well, Access of course is not a spreadsheet; you can use a Query with the
DSum() expression as a calculated field, and display that query on a Datasheet
view form.

I would recommend NOT using either table or query datasheets for interaction
with data. That's not their purpose, and they are of very limited flexibility.
Continuous Forms can be made to look very much like spreadsheets, or you can
use a Datasheet view form.


John W. Vinson [MVP]

 

[Nando]

Well, Access of course is not a spreadsheet; you can use a Query with the
DSum() expression as a calculated field, and display that query on a
Datasheet
view form.

I would recommend NOT using either table or query datasheets for
interaction
with data. That's not their purpose, and they are of very limited
flexibility.
Continuous Forms can be made to look very much like spreadsheets, or you
can
use a Datasheet view form.

Thanks John! But I do not know how to set a continuous form to look like:
OrderPK | Client | Comment | SumOfTotal

What would the record source for the form be then? An inner join query? Or
just the ORDER table and then I should use a textbox with the DSum() right?
I feel I'm missing something.

 

[John W. Vinson]

Thanks John! But I do not know how to set a continuous form to look like:
OrderPK | Client | Comment | SumOfTotal

What would the record source for the form be then? An inner join query? Or
just the ORDER table and then I should use a textbox with the DSum() right?
I feel I'm missing something.

A query (without a Join) using the DSum function instead of a subquery:

SELECT OrderPk, Order.Client, Comment,
DSUM("Total","OrderDetails","OrderDetails.OrderFK = " & Orders.OrderPK) AS
SumOfTotal
FROM Orders;

Use this query as the Recordsource of a form, and set the form's default view
to Datasheet; or to Continuous, and arrange the textboxes up at the top edge
of the detail section, with the bottom edge snugged up against the bottom of
the textboxes.

John W. Vinson [MVP]

 

[Nando]

A query (without a Join) using the DSum function instead of a subquery:

SELECT OrderPk, Order.Client, Comment,
DSUM("Total","OrderDetails","OrderDetails.OrderFK = " & Orders.OrderPK) AS
SumOfTotal
FROM Orders;

It worked great! Thanks a lot John!!!

 

[John Spencer]

Using the DSUM function:

Assumption:
OrderPK is a number field.

SELECT OrderPk, Order.Client, Comment,
DSUM("Total","OrderDetails","OrderFK =" & Orders.OrderPK)
FROM Orders

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Nando]

Using the DSUM function:

Assumption:
OrderPK is a number field.

SELECT OrderPk, Order.Client, Comment,
DSUM("Total","OrderDetails","OrderFK =" & Orders.OrderPK)
FROM Orders

Thanks John!!