How do I get yesterdays balance to be reflected in today`s balance. 3

[R00K]

Here is my issue:

Date B1 B2 B3 What I Want
1/1/02 100 5 105 105
1/2/02 100 3 103 108
1/3/02 100 0 100 108
1/4/02 250 -10 240 348


B1 is a running total of A1-A2

B2 is another number

B3 should be the previous day's balance plus today`s changes or -What I Want-.

I would post the code, but it is a mess and would be a distraction.

Thank you.

David

 

[PaulBricker]

Hope your holiday was good as well. I've got to try and remember where we were with this. I've been trying to work thru some learning issues with Access Projects and MSDE. Access seems easy by comparison.
Can you post the sql from the third query? That would help.

Paul

 

[R00K]

This is from one of your earlier posts and is dientical to the sql I am using.

SELECT qryStartingBalance.Date, Sum(qryStartingBalance_1.Expr1) AS SumOfExpr1
FROM qryStartingBalance, qryStartingBalance AS qryStartingBalance_1
WHERE (((qryStartingBalance_1.Date)<[qryStartingBalance].[Date]))
GROUP BY qryStartingBalance.Date;

 

[R00K]

Paul,

I tried a few things and no luck. The above sql gives me: System resource exceeded. I no longer get the previous error. I tried another computer with twice the perfomance and received the same resource exceeded error.

David

I think this is definately the way to go.

 

[PaulBricker]

David, it may be a A2003 issue but I can't be sure. I looked up the Query Too Complex error and it gave me some indication that it might be a Stack Size issue. But there was a lot of other stuff about ODBC and some limitations there. Because we have built this query on two other ones, we are dragging a lot of baggage along with us. I know the query worked on my PC but I'm running 2000 and I didn't use very many records to accomplish it. I'm wondering if you could try running a similar query to the last one posted but just running it on a table with some make believe data. Just to see if it's the query design or the size that is an issue. Basically you just have two fields like this:

Date MyVal
1/1/02 105
1/2/02 3
1/3/02 0
1/4/02 -10

I would add a few more records to the table.

Call the table MyTemp and the SQL would be

SELECT MyTemp.Date, Sum(MyTemp.MyVal) AS SumOfMyVal
FROM MyTemp, MyTemp AS MyTemp_1
WHERE (((MyTemp_1.Date)<[MyTemp].[Date]))
GROUP BY MyTemp.Date;

Just run that and see if you get an error with that.

Paul

 

[R00K]

Paul,

No error on the last bit of sql. I modified a copy of one of the tables I am using. The numbers of rows in the MyTemp table is the same so there was a lot of data to churn out.

David

 

[PaulBricker]

David, just as an aside, would it be possible to do the final calculations in a Report. You could use the Union Query as the Record Source and then set the Running Sum property to Yes for the Field Expr1. That would give you your running sum the way you need it.
I'm still thinking about the rest of it.


Paul

 

[R00K]

I'll try it out and post again.

 

[R00K]

Itried out the report option. While I see the value in the sum function in the text filed properties (I will use it in future), I couldn't get the functionality I was after.

What is happening in the final query. If I understood more about it, I might be able to figure out another way. Here is the problematic sql:

SELECT qryStartingBalance.Date, Sum(qryStartingBalance_1.Expr1) AS SumOfExpr1
FROM qryStartingBalance, qryStartingBalance AS qryStartingBalance_1
WHERE (((qryStartingBalance_1.Date)<[qryStartingBalance].[Date]))
GROUP BY qryStartingBalance.Date;

Is there a problem in this line: Sum(qryStartingBalance_1.Expr1) AS SumOfExpr1 ?

 

[R00K]

Paul,

I should have posted it earlier:

Query is too complex. (Error 3360)
The query is too complex. Reduce the number of fields in the SELECT clause or the number of subqueries or tables in the join.

The above is the error message &quot;help&quot; text.

To answer my own question in my previous post, isn't the third query, QryRunningBalance, a circular reference?

I don't know why it would work for Access 2000 and not 2003 unless 2003 has some performance rules that prohibit complex queries. I'm going to go back through from the beginning as it may be that there is a problem in an earlier query.

Thanks again Paul.

David

 

[PaulBricker]

No. Basically you set up two recordsets in the query. The first recordset controls what is being summed based on the Date. The second recordset looks at the Date in the first recordset and sums all the values in the field Expr1 of the second recordset up to the Date indicated by the first recordset. Then the query moves to the next record in the first recordset and does the process all over.
It might help to add an Order By clause to the SQL

SELECT qryStartingBalance.Date, Sum(qryStartingBalance_1.Expr1) AS SumOfExpr1
FROM qryStartingBalance, qryStartingBalance AS qryStartingBalance_1
WHERE (((qryStartingBalance_1.Date)<[qryStartingBalance].[Date]))
GROUP BY qryStartingBalance.Date
ORDER BY qryStartingBalance.Date;

The queries are correct as far as I can see. They run for me so the syntax is right. You seemed to confirm that when you ran just the one query based on the table and didn't get an error. So what to do. You could append the records from the Union Query to a Temp Table and then try running the third query against that. That way, the other queries don't have to be running to produce records. You would have to automate it from a Form probably. You'd have to run a Delete query against existing records in Temp Table, then run the Append Query to add fresh records to the Temp table, and then run the Final query against that. Seems like the long way around but once it's automated, it would run quickly. The real question I have is whether it's an A2003 problem. Do you have a machine running 2000 around that you could test the queries on. If not, you could email the Db to me
pbricker@comcast.net
and I could do it here but I'm not sure that would solve the issues.

Paul

 

[R00K]

Paul,

Thanks for all of you help on this. You have put a lot into it.

I am going to try the long way round as the ORDER BY qryStartingBalance.Date; had no effect. Alternately, I may start over as I am really learning as I go and realize that some tables and previous queries could be designed better.

Thanks again.

David

 

[PaulBricker]

Good luck. Keep us posted.

Paul

 

[R00K]

I figured it out.

I'll post the deatail tomorrow.

Thanks again for the help.

 

[R00K]

Paul,

I started over and tried to improve the overall strucure of the database, especially the queries. I reduced the total number of queries by 60% or so. There were a lot of queries. In my ignorance, I was making a query for every equation rather than creating fewer more functional queries.

I also combined some tables that really should have been together in the first place. Again this reduced the leg work to get to the data in the form I wanted it.

Lastly I ran the 3rd query of the 3 you provided, substituting the previous 2 with preexisting queries, and bingo!

I'm sure I am not out of the woods yet, but this is a huge step.

Thank you.

David

 

[PaulBricker]

That is great. Sounds like the woods aren't so deep any more and it sounds like you made some good improvements to the structure of your records which will always work to your advantage. I'm away the next couple days. Will check back in when I get home.
Happy New Years.

Paul