Tags: access, asking, database, dynamic, field, figure, longtime, microsoft, mysql, oracle, query, run, sql, sum, worth

Dynamic run on sum field in query

On Database » Microsoft Access

3,186 words with 1 Comments; publish: Mon, 31 Dec 2007 00:29:00 GMT; (25093.75, « »)

Hello i have been trying to figure out this problem for a very long

time now, and thought maybe its worth asking for some help.

I want to figure out if there is a way to create a field in a select

query that acts as a "COUNT" for the fields in the query.

No matter what order the rest of the fields will be it will always

remain in numerical order from 0+. Field one will be 1, field 2 will be

2, field 3 will be 3...etc.

The reason i need to know this is because i have a run on sum for a

balance field for a table called "tbltrans" where i have got it to

work, but only when the ID is in an assending order.

my sql statement:

-------------------

SELECT t1.id, t1.charge, t1.pay, t1.date, [t1].[charge]-[t1].[pay] AS

due, Sum([t2].[charge]-[t2].[pay]) AS balance

FROM tbltrans AS t1, tbltrans AS t2

WHERE ((([t2].[id])<=[t1].[id]))

GROUP BY t1.id, t1.charge, t1.pay, t1.date, [t1].[charge]-[t1].[pay];

-------------------

If i decide to change the order of any field than this changes the

order of the ID, and the balance since every field is linked to

eachothers order. I need the balance field which is a run on sum, to be

dynamic. This means it is not linked to the rest of the field. the

balance will change according to the order it is in.

Is there a way i can get a field that is constantly in numerical order

from 0+ and it will never change. It will not be linked to the rest of

the fields. Once i can accomplish that than in i can change my "WHERE"

SQL statement to be like:

(the new count field will be named [count] for this example.)

-------

WHERE ((([t2].[id])<=[t1].[count]))

-------

quick demo to explain what i mean:

ID ASSCENDING ORDER

id / charge / pay / balance

----------

1 / 50 / 0 / 50

2/ 0 / 5 / 45

3/ 60 / 0 / 105

4/ 0 / 10 / 95

ID DESSCENDING ORDER

id / charge / pay / balance

----------

4 / 0 / 10 / 95

3/ 60 / 0 / 105

2/ 0 / 5 / 45

1/ 50 / 0 / 50

Notice how the balance has followed the rest of the fields.

I want to get the balance to dynamically recalculate correctly.

If i succeed it should look like:

ID DESSCENDING ORDER *CORRECT WAY

id / charge / pay / balance

----------

4 / 0 / 10 / -10

3/ 60 / 0 / 50

2/ 0 / 5 / 45

1/ 50 / 0 / 95

Also note that the laste field should always show the final sum of the

balance.

It is important i figure this out, so any help would be highly

appreciated!

Thanks,

Gil

All Comments

Leave a comment...

  • 1 Comments
    • i just found out a way to have a dynamic record counter in the query by

      a previous post:

      -----------

      Global gIncNum As Long

      Function ZeroIncNum()

      gIncNum = 0

      End Function

      Function IncNum(Dummy As Variant) As Long

      gIncNum = gIncNum + 1

      IncNum = gIncNum

      End Function

      'Then add two fields to your Query:

      'Expr1: ZeroIncNum()

      'RecNum: IncNum (ID)

      ------------------

      (ID means the tables ID that you want to count)

      RecNum brings you the number of the record, but it doesnt work in a my

      join query. It begins skipping numbers.

      Any ideas?

      #1; Mon, 31 Dec 2007 00:31:00 GMT