Hi Steve,
Ive been trying to implement your suggested solution and I am now focusing on
the interest calculation. To give you an overview of the problem - the
database is attempting to capture details of a stock broker's dealings. I
have a "client" table containing contact details for each client. I have a
"charge rates change" table which contains the commission and interest rates
for each client as they change e.g.
charge client rate date_became_current
Interest claire 0.12 21/1/06
Interest claire 0.12 23/1/06
USD Commission claire 0.04 21/1/06
I also have a "trade" table and "trade" form. A trade happens in two stages:
open and close. so in each record there is "close date," "close share price",
"close no of shares" and "open date," "open share price", "open no of shares".
the daily interest for a trade is: ((open price+close price)/2)*close no of
shares*(interest rate/365)
the problem is that the interest rate may change between the day the trade is
opened and the day it is closed. the interest is earned daily and I need to
be able to see how much was earned each month, which is problematic if the
open date and close date are in different months.
I think I know how to theoretically calculate the total interest for a trade
but I dont know how to code in access vb:
todays_rate is a double, =0
interest_today is a double, =0
i is a date
for i between [forms]![trade]![open date] and [forms]![trade]![open date] ,
i++
{
select "charge rate change" table where [charge] = "Interest" and [client]
= [forms]![trade]![client_reference]
j is a record
for j between first record and last record in [charge rate table], j++
{
todays_rate = if (date(i) is between record(j).date_became_current and
record(j+1).date_became_current,
record(j).rate, 0)
}
interest_today = (todays_rate/365)*(([forms]![trade]![open price]
+[forms]![trade]![close price])/2) *[forms]![trade]![close no
of shares]
interest_to_date = interest_to_date + interest_today
}
(presumably this would be run in the trade form so can I use [Me].
[client_reference] instead of forms]![trade]![client_reference] ??)
that code would calculate the correct amount of interest for the trade but I
still have no idea how I would calculate how much of the interest was earned
in each month.
I very much appreciate the help you've given me so far on this Steve and if
you could give me any suggestions that'd be fantastic.
kind regards
Claire Rohan
Steve said:
Claire,
I am not able to comment specifically, as I still know virtually nothing
about the data you are working with. Even if I did, I suspect it would
be rather complex. But in essence, you need a way to identify the
correct rate based on the date of the Transaction (I don't know what you
call it, but I guess the concept of Transaction fits with what you are
doing?)
One approach is to use a subquery, for example something like this...
CommissionRate: (SELECT TOP 1 [CommRate] FROM [Rates] WHERE
[RateDate]<=[TransactionDate] ORDER BY [RateDate] DESC)
However, possibly the best idea here would be to make a User-Defined
Function to return each of the variable rates. Something like this...
Public Function Comm(TheClient As Long, TransDate As Date) As Currency
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT CommRate FROM Rates WHERE
ClientID=" & TheClient & " And RateDate<=" & CLng(TransDate) & " ORDER
BY RateDate DESC")
Comm = rst!CommRate
rst.Close
Set rst = Nothing
End Function
And then in your queries, or calculated controls on forms or reports,
you use somehting like this...
CommissionAmount: [TransactionAmount]*Comm([ClientID],[TransactionDate])
This is what I want it to do I think:
[quoted text clipped - 5 lines]
For [Currency]![currency_id] = Forms![Trade History]![currency_form]