Please help.
GOAL: I hope to calculate a total amount in a TotalQuery (or field in
the SubQuery?), based upon three field amounts in a SubQuery.
STATUS: When I create an expression in the TotalQuery based upon
SubQuery fields, e.g:
Total: [SOPAmount] + [PetFilingAmount] + [SummonsFilingAmount]
'The resulting [Total] field displays each of the three SubQuery
amounts sequentially in the same field [156.00 50.00 5.00], rather than
the sum of having added them. However, when I test the expression
using multiplication (*) the [Total] field displays the correct total
amount. For example, the expression:
Total: ([SOPAmount]*[PetFilingAmount])+ [SummonsFilingAmount]
'yields a correct single total amount, [7505.00].
The SubQuery in ms-access 2k sums the amounts from certain checks
entered for specific purposes. All resulting records from the Subquery
are sorted on a [MergeDate] field:
The Subquery SQL statement is as follows:
SELECT FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose]
= 'Service of Process' "),2) AS SOPAmount,
FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose] =
'Petition Filing Fees' "),2) AS PetFilingAmount,
FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose] =
'Summons Filing Fees' "),2) AS SummonsFilingAmount
FROM tblDebtorMasterFileList
WHERE (((tblDebtorMasterFileList.MergeDate)=#1/14/2005#));
I don't work with Access all the time, so I am not that astute at these
queries. Could someone please advise me on the best method to obtain a
TotalFees based upon the added total from the three field amounts in
the underlying query.