Can an update query return a calculated value to a select query?

[Guest]

Access 2003

I have a form (Quote-Calculator) containing numerous subforms that are
linked by Quote_# and are each pulled into my subform by a select query. Each
subform is based on a table where the user enters the [Quantity] and [Rate]
for each record of the table. My MaterialPurchases_DataQuery_Subform contains
the following fields:

[Material/Labour] - the name of the material/abour being purchased (eg. Sag
Rods)
[Units] - the units of measurement (eg. each)
[Quantity] - the number of units being purchased
[Rate] - the cost per unit
[Total] - an expression in the query only, not the underlaying data table
[Quote_#] - a unique ID number that links to my main form
{The [Material/Labour], [Units] and [Quote_#] are set values pulled from the
underlying data table, and the [Quantity] and [Rate] have default values that
are adjusted when the user keys in the estimated [Quantity] and [Rate] for
each [Material/Labour] shown in the table.}

Most of the data is straight forward entry into the [Quantity] and [Rate]
fields. However, my "Fabricated Anchors" record ([Material/Labour] field) is
based on another subform (AnchorBolts_DataQuery_Subfrom) that contains the
following fields:

[Size] - the size of anchor bolt (there are approx. 20 sizes)
[Qty] - the number of anchor bolts required (for each size0
[$PerBolt] - the cost of that size of anchor bolt
[Total] - the total cost per size of bolt (This field is an expression in
the select query. It is not in the underlying table)
[Quote_#] -a unique ID number that links to my main form
{Once again, the [Size] and [Quote_#] are set values pulled from the
underlying data table, and the [Qty] and [$PerBolt] have default values that
are adjusted when the user keys in the estimated [Qty] and [$PerBolt] for
each [Size] shown in the table.}

My Problem: I need to create an update query that sums the [Total] field
from the AnchorBolts_DataQuery_Subform and updates that total to the [Total]
for "Fabricated Anchors" in the [Material/Labour] field of my
MaterialPurchases_DataQuery_Subform. Is this possible?

 

[[MVP] S.Clark]

Queries update tables, not subforms. You can update the table of which the
subform is based.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Access 2003

I have a form (Quote-Calculator) containing numerous subforms that are
linked by Quote_# and are each pulled into my subform by a select query.
Each
subform is based on a table where the user enters the [Quantity] and
[Rate]
for each record of the table. My MaterialPurchases_DataQuery_Subform
contains
the following fields:

[Material/Labour] - the name of the material/abour being purchased (eg.
Sag
Rods)
[Units] - the units of measurement (eg. each)
[Quantity] - the number of units being purchased
[Rate] - the cost per unit
[Total] - an expression in the query only, not the underlaying data table
[Quote_#] - a unique ID number that links to my main form
{The [Material/Labour], [Units] and [Quote_#] are set values pulled from
the
underlying data table, and the [Quantity] and [Rate] have default values
that
are adjusted when the user keys in the estimated [Quantity] and [Rate] for
each [Material/Labour] shown in the table.}

Most of the data is straight forward entry into the [Quantity] and [Rate]
fields. However, my "Fabricated Anchors" record ([Material/Labour] field)
is
based on another subform (AnchorBolts_DataQuery_Subfrom) that contains the
following fields:

[Size] - the size of anchor bolt (there are approx. 20 sizes)
[Qty] - the number of anchor bolts required (for each size0
[$PerBolt] - the cost of that size of anchor bolt
[Total] - the total cost per size of bolt (This field is an expression in
the select query. It is not in the underlying table)
[Quote_#] -a unique ID number that links to my main form
{Once again, the [Size] and [Quote_#] are set values pulled from the
underlying data table, and the [Qty] and [$PerBolt] have default values
that
are adjusted when the user keys in the estimated [Qty] and [$PerBolt] for
each [Size] shown in the table.}

My Problem: I need to create an update query that sums the [Total] field
from the AnchorBolts_DataQuery_Subform and updates that total to the
[Total]
for "Fabricated Anchors" in the [Material/Labour] field of my
MaterialPurchases_DataQuery_Subform. Is this possible?

 

[Guest]

Thank-you for your response. I did know that, but had forgotten. I've been
trying this a couple of ways and I have tried putting a [Total] field in the
Material/Labour_Data table, but I still can't seem to create a query that
will update the total correctly to that one field. I have created a select
query which sums the individual totals (by bolt size) of the
AnchorBolts_DataQuery (which is based on the AnchorBolts_Data table), but
when I try to update the [Total] for "Fabricated Anchors" (one record in the
Material/Labour_Data table) with this value it doesn't work. Do you have any
suggestions? As I am a new user, the logic behind my query may not be
accurate.

Queries update tables, not subforms. You can update the table of which the
subform is based.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Access 2003

I have a form (Quote-Calculator) containing numerous subforms that are
linked by Quote_# and are each pulled into my subform by a select query.
Each
subform is based on a table where the user enters the [Quantity] and
[Rate]
for each record of the table. My MaterialPurchases_DataQuery_Subform
contains
the following fields:

[Material/Labour] - the name of the material/abour being purchased (eg.
Sag
Rods)
[Units] - the units of measurement (eg. each)
[Quantity] - the number of units being purchased
[Rate] - the cost per unit
[Total] - an expression in the query only, not the underlaying data table
[Quote_#] - a unique ID number that links to my main form
{The [Material/Labour], [Units] and [Quote_#] are set values pulled from
the
underlying data table, and the [Quantity] and [Rate] have default values
that
are adjusted when the user keys in the estimated [Quantity] and [Rate] for
each [Material/Labour] shown in the table.}

Most of the data is straight forward entry into the [Quantity] and [Rate]
fields. However, my "Fabricated Anchors" record ([Material/Labour] field)
is
based on another subform (AnchorBolts_DataQuery_Subfrom) that contains the
following fields:

[Size] - the size of anchor bolt (there are approx. 20 sizes)
[Qty] - the number of anchor bolts required (for each size0
[$PerBolt] - the cost of that size of anchor bolt
[Total] - the total cost per size of bolt (This field is an expression in
the select query. It is not in the underlying table)
[Quote_#] -a unique ID number that links to my main form
{Once again, the [Size] and [Quote_#] are set values pulled from the
underlying data table, and the [Qty] and [$PerBolt] have default values
that
are adjusted when the user keys in the estimated [Qty] and [$PerBolt] for
each [Size] shown in the table.}

My Problem: I need to create an update query that sums the [Total] field
from the AnchorBolts_DataQuery_Subform and updates that total to the
[Total]
for "Fabricated Anchors" in the [Material/Labour] field of my
MaterialPurchases_DataQuery_Subform. Is this possible?