Inserting a date clause for running total

[din2005]

Hi how do i include a where clause so it adds up the value between ReqDate.

e.g

RunningTotal: Sum(DSum("OrderValueIncVat","tblpreorder","PreOrderID <= " & [PreOrderID]))where between reqdate Startdate and endDate.

many thanks

 

[lespaul]

what's the rest of the query? (switch to SQL view and paste)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[din2005]

Thankyou for replying....here its is! I tried puting the dates in the reqDate field but the running total still adds up everything regardless of filtering it using the dates in the reqdate

Hope that makes sense?


SELECT tblPreOrder.PreOrderID, tblPreOrder.SupplierID, tblPreOrder.SupplierName, tblPreOrder.ReqDate, tblPreOrder.ReqNo, tblPreOrder.ReqPoinNo, tblPreOrder.FinanceCode, tblPreOrder.LocationCode, tblPreOrder.Department, tblPreOrder.ReqBy, tblPreOrder.ItemDetails, tblPreOrder.ItemCode, tblPreOrder.QtyReq, tblPreOrder.[Stock/NonStock], tblPreOrder.ExcLVAT, tblPreOrder.IncLVAT, tblPreOrder.vatValue, tblPreOrder.discount, tblPreOrder.OrderValueIncVat, tblPreOrder.OrderValueExcVat, tblPreOrder.budgFinanYear, tblPreOrder.startDate, tblPreOrder.endDate, tblPreOrder.OrderNumber, tblPreOrder.OrderDate, tblPreOrder.BuyerName, tblPreOrder.BuyerExtenNum, tblPreOrder.QuoteRef, tblPreOrder.DeliveryDate, tblPreOrder.DeliveryComments, tblPreOrder.DeliveryOutcome, Sum(DSum("OrderValueIncVat","tblpreorder","PreOrderID <= " & [PreOrderID])) AS RunningTotal
FROM tblPreOrder
GROUP BY tblPreOrder.PreOrderID, tblPreOrder.SupplierID, tblPreOrder.SupplierName, tblPreOrder.ReqDate, tblPreOrder.ReqNo, tblPreOrder.ReqPoinNo, tblPreOrder.FinanceCode, tblPreOrder.LocationCode, tblPreOrder.Department, tblPreOrder.ReqBy, tblPreOrder.ItemDetails, tblPreOrder.ItemCode, tblPreOrder.QtyReq, tblPreOrder.[Stock/NonStock], tblPreOrder.ExcLVAT, tblPreOrder.IncLVAT, tblPreOrder.vatValue, tblPreOrder.discount, tblPreOrder.OrderValueIncVat, tblPreOrder.OrderValueExcVat, tblPreOrder.budgFinanYear, tblPreOrder.startDate, tblPreOrder.endDate, tblPreOrder.OrderNumber, tblPreOrder.OrderDate, tblPreOrder.BuyerName, tblPreOrder.BuyerExtenNum, tblPreOrder.QuoteRef, tblPreOrder.DeliveryDate, tblPreOrder.DeliveryComments, tblPreOrder.DeliveryOutcome
ORDER BY tblPreOrder.ReqDate DESC , Sum(DSum("OrderValueIncVat","tblpreorder","PreOrderID <= " & [PreOrderID])) DESC;

 

[lespaul]

So you want a total by OrderID? Or a total of ALL orderIDs?

can you show us what you want the results to look like?

 

[din2005]

i want it to add the next result e.g


ordervalue Running toal
15.50 15.50
25.00 40.50
10.00 50.50

and so on etc (i want the total by orderID)

thanks... i want the total by orderID

 

[din2005]

Hi everyone...am still stuck on this query....any suggestions.

As lespaul mentioned....i would like the total by OrderID?

many thanks