I'm using Access 97 and need some assistance in sorting out a proper
DSum expression, or maybe even DCount might be an alternative. I have
tried numerous combinations, but with no apparent success.
The DSum function relates to a subform (sfrmCost) with a text box
(RefCost) which is intended to be the container for a total from a
query (qryItems). The query has three number columns from the table
(tblItems) of which I have an expression with NZ to provide the
subtotals (QSub) for the individual rows.
What I'm trying to accomplish is to use a DSum expression to tally the
(QSub)'s by like (IDRef) numbers. Currently, if there is only one row
with the same IDRef then the (QSub) represents all cost for it and
does post correctly to (sfrmCost) which in turn is posted to the
(TotalItemCost) text box on the main form (frmMain). The
(TotalItemCost) box on (frmMain) uses
=[Forms]![frmMain]![sfrmCost]![RefCost] to obtain the total from
(sfrmCost).
I have also tried using DSum expressions in the control source of the
(RefCost) of (sfrmCost) and in the control source of the
(TotalItemCost) of (frmMain), but this hasn't work either.
Excuse me, but it seems that I have created a somewhat convoluted
tangent above, so allow me to recap:
Table: (tblItems)
(IDRef) - a manually keyed ID # referencing a record on the
main form
Query: (qryItems)
(QSub) - contains the subtotal of one calculated row of three
items - Dsum needed to calculate the QSub totals by the same (IDRef)
when there is more than one row of same.
SubForm: (sfrmCost) - shows all IDRef's and other columns from
tblItems -
(RefCost) - a text box that currently shows the total of
=[Col1]+[Col2]+[Col3] by individual row
Form: (frmMain) - to show the final result of the DSum total by ID
reference.
(TotalItemCost) - the text box to show the total cost for all,
but currently only a one row occurrence using:
=[Forms]![frmMain]![sfrmCost]![RefCost]
Perhaps I should ask which is better to use: an intermediate query or
a control source? Here are a couple of DSum expressions that I have
used:
' in Query =DSum("[QSub]","qryItems","[IDRef] = & Me![IDRef]")
' on Main Form TotalItemCost=DSum("[QSub]","qryItems","[IDRef]=
[Forms]![frmMain]![sfrmCost]![RefCost]")
Any assistance provided will be greatly appreciated. Thanks, Rolan