If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculated fields
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 Whe 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 |
#2
|
|||
|
|||
Calculated fields
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 |
#3
|
|||
|
|||
Calculated fields
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. -- rpw |
Thread Tools | |
Display Modes | |
|
|