Database normalization/Calculations

[DJTI via AccessMonster.com]

Hi, I am a new user having problems with calculations in my database. After
reading many posts I understand that it is not good practice to store
calculations in tables, therefore I need help with my design.

I have a database project for Shareholders and their investments which is
used to calculate quarterly dividends, and redemptions (partial or complete).

Table1:
Shareholders
ShareholderID
Name
Address...

Table2
Investments
InvestmentID
InvestmentDate
InvestmentAmt
ShareValue

Table3
InvestmentDetails
InvDetailID
QuarterDate
QDivValue
QCashPdValue
CurShareValue
RedeemDate
RedeemAmt

I have a transaction form to show each shareholders activity. The two
calculations I am struggling with are Cumulative Shares and Total Value. I
believe the problem I cannot solve is that I do not know how to get a
calculation from a calculation.

Total Value is calculated in a text box on the transaction form with the
calculation =[Cumushares] * [CurShareValue], to get Total Value.

However I struggle with how to derive with my Cumulative Share total.
Cumulative Shares are based on a previous Cumulative Share and all
caculations stem from Cumulative shares.

ex. To find Dividend Earned: =Round([QDivValue]*[CumuShares],2)
Then Dividend earned is added to Cumushares to arrive at a new Cumushares for
the next quarter calculation.

CumuShares is: [Total Value] / [CurShareValue]

Any help to guide me in the right direction will be greatly appreciated.
Thanks,
DJTI

 

[Jeff Boyce]

Instead of doing your calculations in the form, consider doing them in a
query. That way, you could create (another) new query, based on the query
that did the calculations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DJTI via AccessMonster.com]

Thank you for directing me Jeff. I am attempting to redesign a database that
has blossomed from a simple address notification db to a calculating values
and my struggle is with normalization and not looking at access as a
spreadsheet. Thanks again.

Instead of doing your calculations in the form, consider doing them in a
query. That way, you could create (another) new query, based on the query
that did the calculations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am a new user having problems with calculations in my database.
After

[quoted text clipped - 50 lines]

Thanks,
DJTI

 

[DJTI via AccessMonster.com]

Jeff,
I still need help. I am in the process of using a query to do my calculations
but I am unsure how to separate the calculations to make them work. I
presently have one query, the sql is below:

SELECT [Investment Details].InvDetailID, [Investment Details].InvestmentID,
[Investment Details].DateTransaction, [Investment Details].CurShareValue,
[Investment Details].QDivValue, [Investment Details].QCashPdValue,
[Investment Details].RedeembyShare, Investment.CumuShares, Round(
[QCashPdValue]*[CumuShares],2) AS CashPdTotal, Round([CumuSHares]*
[CurShareValue],2) AS TotalValue, Round([QDivValue]*[CumuShares],2) AS
DivEarned
FROM Investment INNER JOIN [Investment Details] ON Investment.InvestmentID =
[Investment Details].InvestmentID
ORDER BY [Investment Details].DateTransaction;

Thanks again.
Deb

Instead of doing your calculations in the form, consider doing them in a
query. That way, you could create (another) new query, based on the query
that did the calculations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am a new user having problems with calculations in my database.
After

[quoted text clipped - 50 lines]

Thanks,
DJTI

 

[Jeff Boyce]

Deb

I'm not sure what you mean by "separate the calculations to make them work."

What is it about the SQL you included that isn't working?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I still need help. I am in the process of using a query to do my
calculations
but I am unsure how to separate the calculations to make them work. I
presently have one query, the sql is below:

SELECT [Investment Details].InvDetailID, [Investment
Details].InvestmentID,
[Investment Details].DateTransaction, [Investment Details].CurShareValue,
[Investment Details].QDivValue, [Investment Details].QCashPdValue,
[Investment Details].RedeembyShare, Investment.CumuShares, Round(
[QCashPdValue]*[CumuShares],2) AS CashPdTotal, Round([CumuSHares]*
[CurShareValue],2) AS TotalValue, Round([QDivValue]*[CumuShares],2) AS
DivEarned
FROM Investment INNER JOIN [Investment Details] ON Investment.InvestmentID
=
[Investment Details].InvestmentID
ORDER BY [Investment Details].DateTransaction;

Thanks again.
Deb

Instead of doing your calculations in the form, consider doing them in a
query. That way, you could create (another) new query, based on the query
that did the calculations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am a new user having problems with calculations in my database.
After

[quoted text clipped - 50 lines]

Thanks,
DJTI

 

[DJTI via AccessMonster.com]

Jeff,
I have been plugging away and now see that my problem was with logistics. I
successfully corrected my sql statement and get the desired results. Thanks
for responding to my message.
SQL:
SELECT [Investment Details].InvDetailID, [Investment Details].InvestmentID,
[Investment Details].DateTransaction, [Investment Details].CurShareValue,
[Investment Details].QDivValue, [Investment Details].QCashPdValue,
[Investment Details].RedeembyShare, Round([CumuShares]+nz([DivEarned])-nz(
[RedeembyShare]),2) AS Expr1, Round([QCashPdValue]*[CumuShares],2) AS
CashPdTotal, Round([CumuSHares]*[CurShareValue],2) AS TotalValue, Round(
[QDivValue]*[CumuShares],2) AS DivEarned, Round([RedeembyShare]*
[CurShareValue],2) AS RedeemedAmt
FROM Investment INNER JOIN [Investment Details] ON Investment.InvestmentID =
[Investment Details].InvestmentID
WHERE ((([Investment Details].DateTransaction) Between #1/1/2006# And
#12/31/2007#))
ORDER BY [Investment Details].DateTransaction;

Deb

I'm not sure what you mean by "separate the calculations to make them work."

What is it about the SQL you included that isn't working?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I still need help. I am in the process of using a query to do my

[quoted text clipped - 32 lines]