Locking fields linked to other tables

[Claire Rohan]

I have a form which captures details of a trade. It uses details from a
Client table dependant on the client selected, such as commission rate and
interest rate. These rates can change over time though but I need it so that
after a trade has been closed ie all the details entered, the rates should
stay as they were at the time of closing. I need to be able to change the
rates in the Client table so that future trades will use the up to date rates.


Sorry if this is unclear! I really need to get this working asap so if any
one could help itd be much appreciated!

 

[Steve Schapel]

Claire,

In these newsgroups, you will often see admonishments about not storing
calculated values in a table. The type of scenario you are describing
is one of the exceptions to this rule. I can't comment specifically
without more details of the data you are working with, but the trade
amounts which are dependent on the variable commission and interest
rates should be saved in the Trades table, so you may need to add some
fields to the table for this purpose.

Well, that's one approach. Another is to add a Rates table to the
database, which tracks changes to the client's rates over time. Remove
the fields in the Client table for the commission and interest etc, and
put this information in the related Rates table, along with a date field
when the rate became current, and then when there is a rate change, you
enter a new record for that client into the Rates table. Thus, your
queries and form/report-based calculations will always be able to use
the correct rate depending on the date of the transaction.

 

[Claire Rohan via AccessMonster.com]

Steve,

Many thanks for you response - Ive been attempting to implement a Rates table
as you have suggested but Im unsure how to then get the correct rate for use
in the main form. I tried to use a query which selects the rate field in the
Rates table with criteria so that the date the trade is closed matches the
date field in the rates table - well thats what I want to do I think but I
dont know the code! any ideas? I think I should be trying to search in
intervals??

Many thanks

Claire,

In these newsgroups, you will often see admonishments about not storing
calculated values in a table. The type of scenario you are describing
is one of the exceptions to this rule. I can't comment specifically
without more details of the data you are working with, but the trade
amounts which are dependent on the variable commission and interest
rates should be saved in the Trades table, so you may need to add some
fields to the table for this purpose.

Well, that's one approach. Another is to add a Rates table to the
database, which tracks changes to the client's rates over time. Remove
the fields in the Client table for the commission and interest etc, and
put this information in the related Rates table, along with a date field
when the rate became current, and then when there is a rate change, you
enter a new record for that client into the Rates table. Thus, your
queries and form/report-based calculations will always be able to use
the correct rate depending on the date of the transaction.

I have a form which captures details of a trade. It uses details from a
Client table dependant on the client selected, such as commission rate and

[quoted text clipped - 5 lines]

Sorry if this is unclear! I really need to get this working asap so if any
one could help itd be much appreciated!

 

[Claire Rohan via AccessMonster.com]

This is what I want it to do I think:

For (i,num of records,1)

{Where Forms![Trade History]![close_date]
Between [Currency]![date].record(i) And [Currency]![date].record(i-1)
return [Currency]![Rate].record(i)}

For [Currency]![currency_id] = Forms![Trade History]![currency_form]

Steve,

Many thanks for you response - Ive been attempting to implement a Rates table
as you have suggested but Im unsure how to then get the correct rate for use
in the main form. I tried to use a query which selects the rate field in the
Rates table with criteria so that the date the trade is closed matches the
date field in the rates table - well thats what I want to do I think but I
dont know the code! any ideas? I think I should be trying to search in
intervals??

Many thanks

Claire,

[quoted text clipped - 20 lines]

 

[Steve Schapel]

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])

 

[Claire Rohan via AccessMonster.com]

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

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]