Problem with 'running totals' in queries

[klix]

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

 

[klix]

(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'.

 

[RuralGuy]

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

 

[klix]

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.

 

[RuralGuy]

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?

 

[klix]

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.

 

[RuralGuy]

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

 

[klix]

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?

 

[RuralGuy]

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

 

[klix]

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.

 

[RuralGuy]

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 and test the speed. I may play with something too.

 

[klix]

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

 

[klix]

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.

 

[RuralGuy]

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

 

[klix]

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.

 

[RuralGuy]

You're welcome. Glad I could help.