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.

I believe there is a Running Balance in this link (http://www.microsoft.com/downloads/details.aspx?FamilyId=AB5AFF5B-A8BA-4C2B-BAB7-EEA1F953C040&displaylang=en).

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.

No problem. Post back if you still have issues.

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.

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

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]+[b]
Criteria: "the name of the account"

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.

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?

AhhHaa! <slapping forehead>
How about adding a RowNumber (http://www.lebans.com/rownumber.htm) to your query or using the DAO.AbsolutePosition in a UDF?

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.

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.

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

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

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.

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?

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.

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",[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.

Expr1: Serialize("query1","code",[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.

It didn't work. A message pops up saying 'Undefined function Serialize in expression'.Did you import the ProdGetLineNumber module from the demo database?

I had forgotten to import the module. Once I did import it I was able to use Serialize. However then I had a problem referencing that field as part of my DLookup function. Here it is again as I use it:

b: DLookUp("[a]","Am Ex","[N] = " & [N]-1)

N is my quick name for the Serialize field.

I don't know, but could it have to do with the reference to [N]-1

(accidentally pressed 'Post' before I was finished)

...the reference to [N]-1? Could it be that it can't account for nothing being before the first record? (That's my guess with my limited knowledge.)

By the way I meant to add that all I get as a result of my formulas is '#Error'.

If N = 1 then you don't want to do the DLookup() right?

No I don't want to use N=1, I want N=-1 because I have to display the previous record. So I do need the DLookup. But that exact formula <b: DLookUp("[a]","Am Ex","[N] = " & [N]-1)> gave me the solution I was looking for before. Except when the reference numbers were disjointed - which you so greatly helped me solved. I don't know what the issue is now.

Is my formula correct? Might that be the problem? As I said in one of my first posts I'm more familiar with Excel syntax so I'm not sure if what I'm using is what I'm really looking for. But it was what I found in the Microsoft Sample Query you directed me to before.

In the following DLOookup():
b: DLookUp("[a]","Am Ex","[N] = " & [N]-1)
...when N = 1 then you are on the 1st record, right? So you do not need to do the DLookup(), right?

Do you mind looking at what I'm doing? I think that might clear up a few things. Here's the link: http://students.uwf.edu/mcm40/.

If not that's ok. I'll post again an a little while. I want to try one more thing first. But if you've had a chance to look at the db before I do post next, please give me your input. Thanks.

To start with you can not use aliases in calculated fields in a query. Is this db what you had in mind?

Good gracious, that's perfect! I'll add those expressions to my other data once I'm at home and go from there.

Iif () was exactly the addition it needed. Being an Excel person you'd think I could have thought of that. That's why I'm not an expert at Access and you are.

By the way, for future reference what do you mean that you can't use aliases in calculated fields in a query? Where had I done that in my db?

In the query you had as the Balance field [a] - [b] which you will see I changed.

Yes I saw that but didn't really notice it fully at first. I see what you mean.

Well, the formula works perfect, as I said before. I can't thank you enough. I appreciate the help.

The only small issue now is that the numbers display slowly. The query has a noticeable delay calculating the balance. Basically right now the original Table has about 25 transactions listed. About 12 of those are supposed to display in the query. Every time it runs it takes about 3-5 seconds. And that's just a sampling of my transactions; eventually I plan to have hundreds and thousands.

Is it because the query is so complex? I'm just not sure if that's typical for Access. Do you know of any ways to speed the process? Or is that inevitable due to everything involved in the query? If it is inevitable then that's fine; I just want to know for sure. Thanks.

It runs that slow because there is a DLookup() in *every* record but the 1st. DLookup's are not particularly fast and then the scheme needs to do one for every record to get the previous balance. How about trying this DLookup() replacement (http://allenbrowne.com/ser-42.html) and test the speed. I may play with something too.

Thanks. I'll try it out when I'm home in a couple hours and let you know.

I tried to implement and run ELookup () but just couldn't get it. Allen Browne is a bit above my level. I read thru the article a few times and did the best I could. I copied that lengthy code at the bottom of the page and entered it into a module like the ProdGetLineNumber. Then I tried to enter ELookup () to no avail.

I noticed that subquery article also. I tried that as another option but I just couldn't write the syntax correctly.

I tried another version of the eLookup and it did not seem to make much difference in the speed.

Well thanks for trying it. Since my main issue was solved I won't worry about this too much. As I said before thank you very much.

You're welcome. Glad I could help.