Calculated fields

[Flofies]

Hello my name is Carlos, this is the first question I post, if you need more
infro in order to provide some help, please let me know:

I'm creating a Database to obtain the profit / loss of my clients (mutual
funds)
so, with the data I have, I first created a table:
----------------------------------------------------------------------------+
PriceId |FundName| Series | Date | Price |
----------------------------------------------------------------------------+
001 |Fund01 | B1 | 01/01/2005 | 1.236598|
002 |Fund01 | B2 | 01/01/2005 | 1.236585|
003 |Fund01 | B3 | 01/01/2005 | 1.236523|
004 |Fund02 | B1 | 01/01/2005 | 2.369874|
005 |Fund02 | B2 | 01/01/2005 | 2.367796|
... ... ... ... ... |
... ... ... ... ... |
--------------------------------------------------------------------+

There are 8 different series (as of today)
There are 10 different Funds
In a same date I have a different price for each Fund on each Series

So, after following the rules of Normalization I ended up with the following
tables:


-----------------+ +---------------------------+ +--
------------------+
Fund_01 | | Series | | Dates
|
-----------------+ -----------------------------+
+--------------------+
PriceID | Price | |IdSeries | SerName | |DateId | Date
|
-----------------+ +---------------------------+ +-----
---------------+
0001 |1.236598| | 001 | B1 | | 001 |
01/01/2005 |
0002 |1.236451| | 002 | B2 | | 002 |
01/02/2005 |
0001 |1.234587| | 003 | C | | 003 |
01/03/2005 |
0001 |1.232231| | 004 | E | | 004 |
01/04/2005 |
... ... | | ... | ... | | .
.. | ... |
-----------------+ +----------------------------+ +--
------------------+

The formula to get the percentage of profit/loss is (((Fp-Ip)/Ip
)/P)*360

Where: Fp=Final price, Ip= Initial price, P= period expressed in days


So, I need help to:

1.-
Make sure I followed the rules of Normalization right.
2.- Create a query with calculated fields and aliases in order get something
like this:

+----------------------------+-----------------+------------------+-----------
----+-----------------+----------------+
| Customer_name | Fund_Name | Initial date | Final
date | Profit / Loss % |Profit / Loss $ |
+----------------------------+-----------------+------------------+-----------
----+-----------------+----------------+
| Smith, John | R & S 1000 | 01/01/2005 |
12/05/2005 | 5.86 | $ 1,236.15 |
| Roberts, Steve | TTN Plus | 06/21/2005 |
11/05/2006 | 2.35 | $ 913.00 |
| ... | ... | ... | ...
| ... | ... |
-----------------------------+-----------------+------------------+-----------
----+-----------------+----------------+

I already have the table with all the data of the Customers, but that's an
issue I can handle.


Thanks in advance

 

[rpw]

Hi Carlos,

I had difficulty interpreting the layout of your 'after normalization'
tables. I think that you are on the right track, but you might need a few
suggestions. Please correct me if I am wrong, but you have 3 tables as
follows?:

tblFund_01
PriceID
Price

tblSeries
SeriesID
SerName

tblDates
DateID
Date

First issue with the above: "Date" is one of those reserved words for Access
- suggest you use something different like "PriceDate"

Second: I don't think that you need to 'normalize' dates - Maybe the 'Pros'
here can confirm this (if any are watching...)

Third: An alternate suggestion for table layouts.

tblFund (holds the 10 different fund names - or more)
FundID (PK)
FundName

tblSeries (holds the 8 different series names)
SeriesID (PK)
SerName

tblPrice (linking table-1fund can have many series and 1 can have many funds)
FundID | combined
SeriesID | primary
PriceDate | key
Price
(with this setup, you can have Fund#1,SeriesB2 many times but only 1 per date)

Then you would need your customer/price linking table (1 customer can have
many prices and 1 price can have many customers):
tblCustPrices
CustID
FundID
SeriesID
PriceDate (again, the four fields combine to the PK)

I hope this helps...

 

[rpw]

Hello again,

Using my suggested structure, I created 4 queries with the last one
producing what you were asking for. Query number 1 (named
qryCustPricesByDate) essentially produces a view of customer name, fund name,
series name, price date, and price:

SELECT tblCustomers.CustName, tblFunds.FundName, tblSeries.SerName,
tblCustPrices.PriceDate, tblPrices.Price
FROM tblSeries INNER JOIN ((tblFunds INNER JOIN tblPrices ON
tblFunds.FundID=tblPrices.FundID) INNER JOIN (tblCustomers INNER JOIN
tblCustPrices ON tblCustomers.CustID=tblCustPrices.CustID) ON
(tblPrices.FundID=tblCustPrices.FundID) AND
(tblPrices.SeriesID=tblCustPrices.SeriesID) AND
(tblPrices.PriceDate=tblCustPrices.PriceDate)) ON
tblSeries.SeriesID=tblPrices.SeriesID;

Query number 2 (named qryEarliestPrices) filters from query number 1 the
earliest date and price for each cust/fund/series:
SELECT A.CustName, A.FundName, A.SerName, A.PriceDate, A.Price
FROM qryCustPricesByDate AS A
WHERE (((A.PriceDate) In (SELECT Min(B.PriceDate) FROM qryCustPricesByDate
AS B WHERE B.CustName = A.CustName)));

Query number 3 (named qryMostRecentPrices) filters from query number 1 the
newest date and price for each cust/fund/series::
SELECT A.CustName, A.FundName, A.SerName, A.PriceDate, A.Price
FROM qryCustPricesByDate AS A
WHERE (((A.PriceDate) In (SELECT Max(B.PriceDate) FROM qryCustPricesByDate
AS B WHERE B.CustName = A.CustName)));

And the final query (named qryGainLoss) produces the calculation between the
earliest and most recent prices for each cust/fund/series:
SELECT qryEarliestPrices.CustName AS Customer, qryEarliestPrices.FundName AS
Fund, qryEarliestPrices.SerName AS Series, qryEarliestPrices.Price AS
[Purchase Price], qryMostRecentPrices.Price AS [Current Price], [Current
Price]-[Purchase Price] AS [Gain/Loss]
FROM qryEarliestPrices INNER JOIN qryMostRecentPrices ON
(qryEarliestPrices.CustName = qryMostRecentPrices.CustName) AND
(qryEarliestPrices.FundName = qryMostRecentPrices.FundName) AND
(qryEarliestPrices.SerName = qryMostRecentPrices.SerName);

Thanks for asking the question as this was a real learning experience for
me. I hope this works for you.