What is the most efficient way to sum values

[Ian Baker]

Hi
I am curious to hear what others think would be the most efficient way to
sum some values given that in Access (2000, 2002 & 2003) there are many ways
to achieve the same result.

I have a main form with a subform that is bound to a hard query. The main
form (as a continous form) contains records for each bank account and the
subform contains the transactions on the currently selected bank account
record.
Amongst other fields each subform record has:
ctlDr - a calculated field in the underlying query showing the debit on a
bank account
ctlCr - a calculated field in the underlying query showing the credit on a
bank account
ctlCleared - a checkbox field that the user clicks to indicate the payment
has cleared
*The reason for the calculated fields is that if say the value in a
CreditAmount field is negative then display the amount as a positive value
in the Dr field and vice-versa (as this is needed when a transaction has
been reversed)

The main form contains 7 fields:
ctlOpeningBalance - derived from the underlying bank account record
ctlPresDr - a calculated text box that is to show a total amount of ctlDr if
ctlCleared=true
ctlPresCr - a calculated text box that is to show a total amount of ctlCr if
ctlCleared=true
ctlBalance - a calculated text box of
[ctlOpenBalance]-[ctlPresDr]+[ctlPresCr]
ctlUnpresDebits - a calculated text box that is to show a total amount of
ctlDr if ctlCleared=false
ctlUnpresCredits - a calculated text box that is to show a total amount of
ctlCr if ctlCleared=false
ctlEndBal - a calculated text box of
[ctlBalance]+[ctlUnpresDebits]-[ctlUnpresCredits]

I need to create a way that will update each of the main form calculated
fields each time: ctlCleared changes in a subform record
A subform record is deleted
A subform record is added (bank fees, reversals etc)
When the main form record changes

So of the many ways to achieve this which is the most efficient way i.e. a
hard query with totals and code doing a lookup each time, using
RecordsetClone in code, the fields using a DSum() etc. (Note the tables are
attached from a backend)

Your thoughts and examples are greatly appreciated.

 

[Mingqing Cheng [MSFT]]

Hi Ian,

From your descriptions, I understood that you are searching the most
efficient way doing sum from calculated subforms. Have I understood you? If
there is anything I misunderstood, please feel free to let me know

Based of my scope, there are many factors that may lead to efficiency
issue. The best way of finding the most efficient one is making some small
samples. IMO, I am afraid RecordsetClone might be the LEAST efficient way
to do so. Hard Query might be a better one as it may reduce compile times.
Also, LookUp and DSum may be suitable according to your project scenario.

Looking at the nature of this issue, if you want to find the excatly answer
for which one will be better, you have to make us reproduce it on my box
and I am afraid it would require intensive troubleshooting which would be
done quickly and effectively with direct assistance from a Microsoft
Support Professional through Microsoft Product Support Services.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Mingqing Cheng [MSFT]]

Hi Ian,

I wanted to post a quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look forward to hearing from
you!

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Ian Baker]

Thanks Mingqing for your help.

After doing some experimenting I found that the best way seems to me is to
create a subform with the calculated controls and bind them to a hard stored
"Totals" query although the IIf statements are not pretty. For ref here is
the query:
SELECT [Financial Transaction].AccountCode,
Sum(Nz(IIf([Cleared]=True,IIf([AmountDebit]>0,[AmountDebit],IIf([AmountCredi
t]<0,[AmountCredit]*-1))))) AS ClearedDr,
Sum(Nz(IIf([Cleared]=True,IIf([AmountCredit]>0,[AmountCredit],IIf([AmountDeb
it]<0,[AmountDebit]*-1))))) AS ClearedCr,
Sum(Nz(IIf([Cleared]=False,IIf([AmountDebit]>0,[AmountDebit],IIf([AmountCred
it]<0,[AmountCredit]*-1))))) AS UnclearedDr,
Sum(Nz(IIf([Cleared]=False,IIf([AmountCredit]>0,[AmountCredit],IIf([AmountDe
bit]<0,[AmountDebit]*-1))))) AS UnclearedCr
FROM [Financial Transaction]
WHERE ((([Financial Transaction].StatementNo)=[Forms]![Financial
Account]![ctlStatementNo]+1 Or ([Financial Transaction].StatementNo) Is
Null))
GROUP BY [Financial Transaction].AccountCode
WITH OWNERACCESS OPTION;

Thanks again

 

[Mingqing Cheng [MSFT]]

Hi Ian,

Thanks for sharing your experience with us!

Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support