Interest Calculation

[small brother]

Dear All
I need your help to calculate an interest amount for the account payable by
customer.
I have a table containing the following fields:
-Date
-Code
-Name
-Balance

I need to calculate an interest on the third day for the minimum amounmt of
the previous 2 days.

Example

Date Code Balance Interest
01/01/2007 1520 1000 0
02/01/2007 1520 700 0
03/01/2007 1520 600 700*i
04/01/2007 1520 1200 600*i
05/01/2007 1520 1500 600*i
06/01/2007 1520 1800 1200*i

off course i have more than 1 customer.

thanks for your help

 

[Klatuu]

Use an Update query to do this. you can write a function that calculates the
interest and updates the balance and call it from the query.

 

[small brother]

Thank you Klatuu
But your answer in an non answer because if i know how to write the function
do you think that i will ask for it.

Please next time give a complete answer or let the others give their help.

 

[Klatuu]

An attitude like that wont get you much help.
How do you expect me to know your level of competence based on your question?

If you want a complete answer, ask a complete question. For example,
what are the business rules for which records need the calculation?
What is the mathmatical formula for performing the calculation?
Is the interest added to the balance or is it carried in a separate field?

Now, if you want to be a smartass, go somewhere else. If you want a
complete answer, post a complete question.

 

[small brother]

Dear Klatu
You are the smartass!!
My Question is complete.
The example is the result needed (i is the interest needed).

Thank you SMARTASS For your help.
Hope that your next answer will be helpful.

 

[Klatuu]

Let's see what we don't have in the original post:

You mention days, but show values by month:
Date Code Balance Interest
01/01/2007 1520 1000 0
02/01/2007 1520 700 0
03/01/2007 1520 600 700*i

So which is it?

700*i

How do you arrive at 700?

What is i?

Need I go on?

I and many others volunteer our time to help others. We are happy to assist
others so the Access product is promoted and used as well as possible.

If you think the answer is incomplete or you don't understand it. A simple
request for assistance is certainly in order; however, being rude and
insulting someone who is attempting to help you get your job done wont do you
a lot of good.

Maybe you will get others to assist you, provided they only read your first
post.

One last time, If you will describe in detail what you want to do, I will be
happy to assist.

 

[small brother]

My Friend don't be nurvous
I'm sorry if you have tooked what i have said as insult but it isn't.

I'm struggling with this problem for more than 1 week now.

I have asked 1 other question about importing excel tables and it was not
possible for to use your answer.

Anyway for the problem i have wrote :

I need to calculate an interest on the third day for the MINIMUM amount of
the previous 2 days.

So the 700 is the minimum balance for the 1st and second january (the date
format is dd/mm/yyyy).

The 600 is the min between the 2nd and the third and so on.

Please don't forget that the table contain more than 1 customer.

I have putted only 1 to simplify the problem.

Thanks for your time and your help.

 

[small brother]

I is the interest rate

Let's see what we don't have in the original post:

You mention days, but show values by month:
Date Code Balance Interest
01/01/2007 1520 1000 0
02/01/2007 1520 700 0
03/01/2007 1520 600 700*i

So which is it?

700*i

How do you arrive at 700?

What is i?

Need I go on?

I and many others volunteer our time to help others. We are happy to assist
others so the Access product is promoted and used as well as possible.

If you think the answer is incomplete or you don't understand it. A simple
request for assistance is certainly in order; however, being rude and
insulting someone who is attempting to help you get your job done wont do you
a lot of good.

Maybe you will get others to assist you, provided they only read your first
post.

One last time, If you will describe in detail what you want to do, I will be
happy to assist.

 

[Klatuu]

Okay, thanks. Now that I see the complete picture, I don't think it can be
done in a query. I think it will take recordset processing. Give me a
little time, and I will post back with an answer for you I hope you can use.

What question did I answer before that you were not able to use? Perhaps I
can clear that up as well.
It is late in the day here, so it may be tomorrow before I have an answer
for you.

 

[small brother]

Thank you for the time and enerrgy that you are spending
for the other question is about imposrting excel files
the files are named report dd/mm/yyyy.xls
when importing or before i want to pick thats date from the file name and to
add it in a separate field for all the records imported from each table.
Here is the link to your answer.

http://msdn.microsoft.com/newsgroup...84a7&mid=988aaa68-0b51-41ba-af46-66d53b2e0fb0

I have solved the problem of removing the subtotals and automation of the
import.
here is the code need some tunning because it is always openning the first
file only.


Private Sub Command1_Click()
Dim objXL As Excel.Application
Dim wbXL As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim strPath As String
Dim strFileName As String
Dim FILE As String
Set objXL = CreateObject("Excel.Application")
'Set sheet = CreateObject("Excel.sheet")
strPath = "C:\IntCalc\Jan07\"
strFileName = Dir(strPath & "*.xls")
FILE = strPath & strFileName
Set objXL = CreateObject("Excel.Application")


Do While Len(strFileName) <> 0
'objXL.Workbooks.Open FILE
Excel.Workbooks.Open FileName:=FILE
Excel.Worksheets("Sheet1").Range("A:L").RemoveSubtotal
DoCmd.TransferSpreadsheet acImport, , "Sheetimport", FILE, False
Excel.Workbooks.Close
strFileName = Dir()
Loop
objXL.Workbooks.Close
Set objXL = Nothing
End Sub

Thank you Access Guru

Don't think that all the others have your level elses they will not need
your help.

 

[small brother]

Dear Klaatu

I'm waiting your answer about the interest calculation.
All the other issues are solved.