Not understanding Running Sum Query

[dynamictiger]

Hi



I am sure this is simple if you have more experience with running sum queries, however I have never needed one before.

SELECT tblConstOrder.Job, tblConstOrder.OrderNumber, tblConstOrderDetail.Order,
tblConstOrderDetail.Quantity, tblConstOrderDetail.UnitCost, 
[Quantity]*[UnitCost] AS LineCost,
DSum([Quantity]*[UnitCost],"tblConstOrderDetail","[OrderNumber]=" & [OrderNumber]) AS Expr1
FROM tblConstOrder INNER JOIN tblConstOrderDetail ON tblConstOrder.OrderNumber = tblConstOrderDetail.Order;

What I am doing is storing the unit cost and the quantity in my order details table, now I want to calculate the line cost that is q by cost and at the same time know how much I am already commited for in total dollars.

Sounds simple enough to manage in one query as then I can make it the source for teh form and interact with the forms job numbers consequently always on budget (well hopefully anyway).

I could do with some help on DSum([Quantity]*[UnitCost],"tblConstOrderDetail","[OrderNumber]=" & [OrderNumber]) AS Expr1 as it seems no matter what I make OrderNumber or Order I keep getting cartesian products.

[jzwp11]

In your DSum() function, you reference the field OrderNumber, but the OrderNumber field is not in tblConstOrderDetail. The field name in tblConstrOrderDetail referring to the order number is Order. Additionally all parts of the DSum() function that are not variables must be enclosed by double quotes. The [Quantity]*[UnitCost] is not enclosed in double quotes:

What you currently have:
DSum([Quantity]*[UnitCost],"tblConstOrderDetail","[OrderNumber]=" & [OrderNumber])

Should be:

DSum("[Quantity]*[UnitCost]","tblConstOrderDetail","[Order]=" & [OrderNumber])

[weekend00]

you should not use dsum in a query at all.

dsum is a VBA functino, can not be used in query.

[jzwp11]

Weekend00,

I have used the domain aggregate functions in queries and in VBA code. The domain aggregate functions may be a little slower than a nested query from what I have read. Other then being a little slower, I have not heard of any issues why they should not be used. Could you provide some more detail?

[weekend00]

My mistake.

I tried, it did work but extremly slowly