Dcount : Att Graham Seach

[Guest]

Hi Graha
I am creating a small accounts package, i need to have a reconciliation of payments in and out, my qry below works but it is slow in loading
too slow in fac
I you have any suggestions i would appreciate the hel
D

SELECT tblPayments.PaymentID, tblPayments.sequence, Format(FormatNumber([paymentdate]),"00000000") & Format([tblPayments.PaymentID],"000000") AS d, tblPayments.PaymentDate, tblPayments.BankDate, [bankdate] Is Not Null AS isbanked, tblPayments.Banked, tblPayments.Reference, tblPayments.Category, DSum("amount","tblPaymentDetails","[PaymentID]=" & [tblPayments].[PaymentID]) AS pa, IIf([isbanked],[pa],0) AS bankedPayment, tblPayments.receipt, IIf([receipt],[pa],0) AS income, IIf([receipt],0,-[pa]) AS expense, (Val(DSum("bankedPayment","qryReconciliation","[tblPayments.sequence]<=""" & [tblPayments.sequence] & """"))) AS bankedBalance, Format([paymentDate],"yyyymm") AS paymentMont
FROM tblPayments INNER JOIN tblPaymentDetails ON tblPayments.PaymentID = tblPaymentDetails.PaymentI
ORDER BY Format(FormatNumber([paymentdate]),"00000000") & Format([tblPayments.PaymentID],"000000")

 

[[MVP] S.Clark]

Things that are known to slow down queries are:

Functions(e.g. Format()) ,
Instant If's (e.g. IIF()), which is also a function, and
Order By clauses
Domain Functions(DSum, Dcount, etc)

So, examine your SQL and ask yourself if you really "need" each occurance of
these.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi Graham
I am creating a small accounts package, i need to have a reconciliation of

payments in and out, my qry below works but it is slow in loading.

too slow in fact
I you have any suggestions i would appreciate the help
DD

SELECT tblPayments.PaymentID, tblPayments.sequence,

Format(FormatNumber([paymentdate]),"00000000") &
Format([tblPayments.PaymentID],"000000") AS d, tblPayments.PaymentDate,
tblPayments.BankDate, [bankdate] Is Not Null AS isbanked,
tblPayments.Banked, tblPayments.Reference, tblPayments.Category,
DSum("amount","tblPaymentDetails","[PaymentID]=" &
[tblPayments].[PaymentID]) AS pa, IIf([isbanked],[pa],0) AS bankedPayment,
tblPayments.receipt, IIf([receipt],[pa],0) AS income, IIf([receipt],0,-[pa])
AS expense,
(Val(DSum("bankedPayment","qryReconciliation","[tblPayments.sequence]<=""" &
[tblPayments.sequence] & """"))) AS bankedBalance,
Format([paymentDate],"yyyymm") AS paymentMonth

FROM tblPayments INNER JOIN tblPaymentDetails ON tblPayments.PaymentID = tblPaymentDetails.PaymentID
ORDER BY Format(FormatNumber([paymentdate]),"00000000") & Format([tblPayments.PaymentID],"000000");