Running Total in Subform

[jzwp11]

The Q1 and Q2 are aliases I assigned to the nested queries in the qryBase and qryFinal which was just an illustration of how to use nested queries instead of the DSum() functions. The domain aggregate functions generally run a little slower than nested queries from what I have read, but since you are planning on using the query as the record source for a subform, the nested query approach may yield an un-updateable recordset (i.e. you would not be able to add new records to a subform that uses nested queries in it's record source).

[Scorpio11]

i'd be lying if i said i understood everything there, but i'd like to think i am beginning to grasp it, albeit slowly

From what i can see... In the Select function - you are telling it that you want to sum SecTrx from the table alias Q1 and then proceed to decribe to the function what Q1 actually is....? and then start adding the various constraints as well after that...?

Am i even close?

I have done a test or two, but the way the 2 queries have been built will suit me nicely.

[jzwp11]

You're close but just to be clear, the SELECT clause is a part of a query not a function. A function has a different meaning as you probably are aware with the DSum() function. Actually the DSum() function is built from a query. The SELECT clause tells to select those specific fields. The FROM clause provides the source for those fields, i.e. tables or queries. The aliases are necessary because I need to go back to the same table that the main query is accessing; this basically allows SQL to access the same table simultaneously which is necessary to do the running sum.

[Scorpio11]

I have a question.

We were using the descriptions like "Trade", in [SecTrxType] and "Sale" and "Purchase" in [Description] as our constraints when calculating the various running totals.

What if I wanted to elimiate the human error of spelling errors and created a table that links into [SecTrxType] in the TrxDetail form and populate that cell with a pull down menu containing "Trade", "Purchase" "Sale" and so forth.

How would this change affect your formulae - can i still use the "Trade" in the Select clause or would i need to use a different bracket or maybe the Autonumber value?

[jzwp11]

Setting up tables to hold the SecTrxTypes and Descriptions much like the following and then referencing the primary key as a foreign key in the related table would be the preferred way to do it.

tblSecTrxTypes
-pkSecTrxTypeID primary key, autonumber
-txtSecTrxTypeName

Then in your form you could use a combo box based on the table like the above to populate the key value in the related table. You would then use the key value in the queries used to do the calculations.