Changing DSum to SQL statement

Question

I have this DSsum Code now because it is too slow I want to replace it with SQL code , but Im not sure how to do it:

Running Total : DSum("Taxable"," Qrytaxes" ," [EmpID] = &[ EmpID]& " AND [TTDate]<=#" &[TTDate]& " #" )

Now how do I write a valid SQL statement?

Regards

Chris

Answer 1

The most efficient method is a join, e.g.

SELECT QT1.EmpID, QT1.TTDate, QT1.Taxable,

SUM(QT2.Taxable) AS RunningTotal

FROM QryTaxes AS QT1 INNER JOIN QryTaxes AS QT2

ON QT2.EmpID = QT1.EmpID AND QT2.TTDate<=QT1.TTDate

GROUP BY QT1.EmpID, QT1.TTDate, QT1.Taxable;

This will not return an updatable result table, however.  For that you will need to call the DCount function.  You'll find examples of this and other queries to return balances in Balances.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

Answer 2

That would not help, but because you don't have to take my word for it:

(untested)

select sum(Taxable)

from Qrytaxes

where EmpID=[give empid] and TTDate<=#[give ttdate]#;

By the way, you have an extra space before the last # which does not belong.

What will speed up the query is to have indexes on empid and ttdate. Unique if possible, duplicate otherwise.