Problem with 'running totals' in queries

[klix]

I've been trying to create a running total in a query but can't seem to figure it out. I checked multiple sites on the web with no luck. They directed me to use one of two routes: SLQ or the DSum() function.

No matter which one I try to use this message pops up:
Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.

After that I can never try to open that particular query again (even to change it) or else the message pops up and Access restarts.

Let me explain further. I'm trying to keep banking records. This requires me to have a running total to keep the current balance of the account. For instance,
Description--In---Out--Balance
[entry]-----300--------300
[entry1]-----------20--280
[entry2]----40---------320
etc.

Obviously I need to start with the previous entry 'Balance' and either add the current entry 'In' or subtract the current entry 'Out' to create the new 'Balance'.

I'm used to working with Excel, so that function is extremely easy for me. With the records getting larger every day, and me wanting to manipulate the numbers, you can see how I want to convert to Access. But being fairly new to the program, I'm not up to par on advanced questions of this nature. Maybe it's not actually possible to do what I'm envisioning but I'm not ready to give up yet.

I will appreciate any help.

 

[RuralGuy]

I believe there is a Running Balance in this link.

 

[klix]

Thanks for the reference material. After sorting through it I think it will give me what I need. If not I'll post again. Thank you once more.

 

[RuralGuy]

No problem. Post back if you still have issues.

 

[klix]

Ok I've run into another problem. From the query sample file you directed me to I was able to display the previous record using DLookup() and use that to create a running balance successfully, sort of. I use the function thus:
Previous: DLookUp("[a]","Visa","[#] = " & [#]-1)

The problem now is this:
I have one main Table listing all entries for all accounts. However I have multiple Queries, one for each account. Each Query only displays the information for a particular account. The issue this creates is that the transaction #s (which are used as you can see in the formula above) are not consecutive on the Queries. That means that the formula won't display the 'previous' record from that particular Query, because it's looking for the absolute previous record based on the transaction #.

I guess I need a new formula, or else a new way of referencing the 'previous' record within the Query. But I'm at a loss as to how to do either.

 

[RuralGuy]

What gives the Query an ORDER? Do you have an OrderBy clause?

 

[klix]

I'm not exactly sure what that means. It's probably my unfamiliarity with Access but I don't know what an OrderBy clause is. What I do know is that besides displaying fields from a table, the only additions to the query are three formulas and a 'criteria':
a: [In]-[Out]
b: DLookUp("[a]","Am Ex","[#] = " & [#]-1)
Balance: [a]+
Criteria: "the name of the account"

 

[RuralGuy]

The db engine (Jet) does not present the records in any particular order that you can depend on unless you include an OrderBy clause in the query. Think of the table as just a bucket of records and Jet returns then as fast as possible. There is a sort property in each field of the query builder where you would dictate the order.

 

[klix]

The query comes back sorted already. Sorting it ascended doesn't change anything. The problem is that the transaction # field is not consecutive in the query (because the table includes transactions from different accounts). The result is that the query results of transactions # are disjointed and the formula 'b' above won't display the correct number. I'll show an example below:

#--Account----Out------In--------a--------b--------Balance
6--Checking--5375.01----0---(5375.01)--(nothing)---(nothing)
17-Checking----16.10----0---(16.1)------(nothing)---(nothing)
18-Checking----6.95-----0---(6.95)------"-16.1"-----(23.05)

On the second line above nothing is displayed under field 'b'. I think it's because the # field goes from 6 to 17. However I need to display the field immediately above on every line, regardless of the #.

Is that possible? Might I need a new expression to do that?

 

[RuralGuy]

AhhHaa! <slapping forehead>
How about adding a RowNumber to your query or using the DAO.AbsolutePosition in a UDF?

 

[klix]

Oh gosh. Either and both of those sound good but I'm not sure how to use them. If you could explain how or direct me to somewhere that does (that RowNumber link was confusing) I think I'll be on my way. Thanks.

 

[RuralGuy]

Import the one ProdGetLineNumber module in Stephan Lebans mdb into your db. Then just look at the one query in the example db from Stephan and you will see the extra field that uses Serialize() you need to add to your query.

 

[klix]

I will try that and let you know how it goes. Thanks.

 

[RuralGuy]

Thanks. I'm curious if everything works for you.

 

[klix]

This will sound dumb <shaking head> but I can't find the file you told me to download in order to import the ProdGetLineNumber module. I'm slightly confused. Maybe I'm looking in the wrong place though. I downloaded the zip file on the RowNumber link you sent in post 10 but access won't let me open it. When I try I get a message saying that I'm trying to open a read-only db and the first time you open a db you must be able to make changes to it, etc.

Once I get past my own ineptitude I can get rolling and let you know how it goes. Sorry and thanks again.

 

[RuralGuy]

Did you unzip it first? When that is done then just double click the file and it will either run or want you to convert it. What version of Access are you using and what OS?

 

[klix]

Ok, told you it was my own ineptitude. I haven't dealt with zip files in forever - I forgot to actually unzip it, not just open it. Duh. Post again soon.

 

[klix]

It didn't work. A message pops up saying 'Undefined function Serialize in expression'. Furthermore I looked up Serialize () on the Access homepage and returned no results.

One thing that might be a problem is that I'm not sure of the syntax of that expression. It goes: Expr1: Serialize("query1","code",

).  I don't quite know what "code" is a reference to, but I've tried to fit it to my data in various ways, none of which work.

 

[RuralGuy]

Expr1: Serialize("query1","code",

)
Param1) "query1" - Points to the query from which you are executing.
Param2) "code" - Points to the PrimaryKey of that query.
Param3) [code] - Passes the PrimaryKey value from the current record.

 

[RuralGuy]

It didn't work. A message pops up saying 'Undefined function Serialize in expression'.

Did you import the ProdGetLineNumber module from the demo database?