M
MikeC
I have developed a report in Access 2002 that has two grouping levels,
Record ID and Payment Method. For each transaction record, the report
prints one or more payment methods and the amounts for each payment method.
I am currently trying to find the best way to calculate the customer's
Change Amount. If any portion of a transaction includes a cash payment,
then the report will calculate the customer's Change Amount by subtracting
the Cash payment amount from the Cash Received amount. In the below
example, the Change Amount is calculated as $100.00 - $90.00 = $10.00.
The report will provide the below information for each transaction:
=================================================
Record ID Cash Received Change Amount
123 $100.00 $10.00
Payment Method Amount
Cash $ 90.00
Credit Card $101.00
Money Order $210.00
Total $401.00
=================================================
As you can see above, a transaction record can have one or more individual
payment records and only the Cash portion of the payment is involved in
calculating the Change Amount.
The Record ID, Cash Received and Change Amount controls are contained in the
parent record header section of the report. The Payment Method and Amount
controls are contained in the Payment Method Header section of the report.
QUESTION: What is the most efficient method for calculating the Change
Amount?
I have considered using DSUM to obtain the Cash payment amount and then
subtract this amount from the Cash Received amount which is stored in the
main transaction record. However, I have heard from a number of different
sources that DSUM is relatively inefficient and queries should be used
instead whenever feasible. Other alternatives could be to use DAO or ADO to
perform the calcuation and set the value of the Change Amount text box
control. Which approach is most efficient for Access 2002 to process?
Record ID and Payment Method. For each transaction record, the report
prints one or more payment methods and the amounts for each payment method.
I am currently trying to find the best way to calculate the customer's
Change Amount. If any portion of a transaction includes a cash payment,
then the report will calculate the customer's Change Amount by subtracting
the Cash payment amount from the Cash Received amount. In the below
example, the Change Amount is calculated as $100.00 - $90.00 = $10.00.
The report will provide the below information for each transaction:
=================================================
Record ID Cash Received Change Amount
123 $100.00 $10.00
Payment Method Amount
Cash $ 90.00
Credit Card $101.00
Money Order $210.00
Total $401.00
=================================================
As you can see above, a transaction record can have one or more individual
payment records and only the Cash portion of the payment is involved in
calculating the Change Amount.
The Record ID, Cash Received and Change Amount controls are contained in the
parent record header section of the report. The Payment Method and Amount
controls are contained in the Payment Method Header section of the report.
QUESTION: What is the most efficient method for calculating the Change
Amount?
I have considered using DSUM to obtain the Cash payment amount and then
subtract this amount from the Cash Received amount which is stored in the
main transaction record. However, I have heard from a number of different
sources that DSUM is relatively inefficient and queries should be used
instead whenever feasible. Other alternatives could be to use DAO or ADO to
perform the calcuation and set the value of the Change Amount text box
control. Which approach is most efficient for Access 2002 to process?