Calculation Problem (Opening Balance)

[mkkashif]

Hello Dear
i have a ledger report.i am using overall sum in calculated field.
i want to take balance in Opening Balance Field Before The dates i have given.
Example.
i give two date "5-09-04"" and "8-09-04" it gives me result and it's fine.
the problem is that in" Opening Balance Field" i want the balance of customer before "5-09-04".Mean the balance in on "4-09-04"
how it will done.
i have these fields in my table."CustomerId,Credit,Debit,PaymentType".
Any Help Will Greatly Appreciated.

 

[aleb]

Sounds like you need the running sum query, you can search the threads on this but i will give also an example

SELECT [your date] AS DateAlias, [your amount field] AS DailyCashflow, CCur(DSum("your amount field","your query","[your date]<=#" & Format([your date],"dd/mm/yyyy") & "#")) AS RunningSum
FROM [your query]
ORDER BY [your date];

Then create another query which will pick the selected date from the previous query

 

[mkkashif]

Please Give me a Example File

Dear Sir i am very furstarted .please if you can give a example file.
i will be greatly thankfull to you.i think your help will solve my problem and
remove my headache.
i am not a advance users .please give me a attach file.

 

[aleb]

Here we go:

 

[mkkashif]

Thanks More Question

thanks very much for your kind reply.
ok .
i will create the opening balance by query.i have another query which calculate the all result of ledger for customer.
how can i attach the first query result.and then the second query result together.

i mean in one query i am bringing up the opening balance.
and in another query i am binging up the all record about that customerid.
i want to show the opening balance on top.and then the results of the second query down.
and then want a running sum.
opening balance 100
amountreceived 50
closing balance 50

 

[aleb]

The way you go further is determined by your business needs. Whether you need it to display on the form or your you want this to be on the report.
Both approaches are different. Besides I suspect that you don't need so many query as u stated above.

 

[mkkashif]

Please Help Me

dear Aleb
I am very Furstrated .i will be greatly thank full if you solve my problem
please check the attach file.
there is a query overallsum and a table ledger.
when you open the query overallsum it ask you the date please enter
02-04-04 and 05-04-04 then a customer id.it gives you the result about that customer.but there is error in running sum please solve that and very big problem is that how can i put the opening balance on top line.Mean the balance before 02-04-04.
at that time the balance is 10$.
how can i do that.
again i am thankful for your kind help.

 

[Rich]

Use a Union query

 

[aleb]

LOL the voice from sky "use union query"

 

[mkkashif]

How Can i use the Union Query

Can you give me a example file rich how can i use the union query and how can my problem can solved

 

[mkkashif]

Please Give Me Example File

if i only want to show the opening balance in report how can i done that.
i am using overall sum in Calculated Field.
how can i bring up opening balance in Report.
i don't need it in a Query.

 

[mkkashif]

No Can Give me a Example File

No Can Give Me a example File About this Thread.
i will greatly thankful

 

[mkkashif]

Opening Balance on Report

There is no Function to bring up opening balance on Report.
i think my problem can be solved by some advance users

 

[aleb]

Don't be panic everything can be solved

 

[mkkashif]

You Can Solve My Problem

you can solve my problem.
if you said that i will send a example file in which the result you are demanding .
then i will be very relax.

 

[aleb]

Here we go ...
Here is a sample dbase for you to use.

 

[mkkashif]

I have also this file before

i have this file before i started the thread.but the problem is i don't know how to utilize this for my purpose.if you know exactly.please give me the example how can i bring the opening balance on top.and then ledger.
if it is difficult in query i don't need in Query.i need it in report.
again thanks for you help.
awaiting your reply

 

[aleb]

This db is adopted to your table



Now based on the select day query you can create a report

Let me know if anything you need else

 

[aleb]

Final version with report:

 

[mkkashif]

Thanks Very Much For Your Kind Interest

i am realy greatly thankful for your kind help and kind attention.i have solved the problem.one is by your query and other i have made a query which return the previous date balance and id.with date.
then i made a report which is based on two query.
at the end i have solved my problem .
again thanks for your kind help.
do you know the best site where can i find the sample database.