Most Efficient Calculation Method?

  • Thread starter Thread starter MikeC
  • Start date Start date
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?
 
MikeC said:
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?


First thing is to get something that works, then, if it's
unacceptably slow, worry about how to make it faster.

Without more specific information about the report's record
source data and how the values in the vaious header and
detail sections is derived, it's difficult to guess a "best"
way to calculate something.
 
Back
Top