Re: Dcount : Att Graham Seach
From: [MVP] S.Clark (Steve.Clark_NOSPAM_at_FMSInc.com)
Date: 04/20/04
- Next message: [MVP] S.Clark: "Re: use query or calculated control?"
- Previous message: [MVP] S.Clark: "Re: exporting into e-mail"
- In reply to: DD: "Dcount : Att Graham Seach"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 20 Apr 2004 15:59:25 -0400
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- "DD" <anonymous@discussions.microsoft.com> wrote in message news:E3072757-B677-4AA2-9B07-37F285720034@microsoft.com... > 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"); >
- Next message: [MVP] S.Clark: "Re: use query or calculated control?"
- Previous message: [MVP] S.Clark: "Re: exporting into e-mail"
- In reply to: DD: "Dcount : Att Graham Seach"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
