Good morning/afternoon/evening to you all,
First: I'm relatively new to Microsoft Access. I've done a fair bit of research (read through every link in the post by Micro found here) and I have some database management experience from when I was in University. The first thing I did was to write in plain English the requirements of the database. Here is what I came up with:
The company requires a fund manager tracking database. They want to track of information about funds, their mandates (which are essentially 'portfolios of investments'), the companies that the individual mandate has invested in, transactions for each mandate from their internal accounting system, and information contained on quarterly manager reports. For generating reports, the company also needs to keep track of daily foreign exchange rates and benchmark returns (however they do not require relationships to the data (at least from my understanding, as the numbers just need to be referenced in currency exchange calculations when reports are generated).
· Required fund information: fund name
· Required mandate information: mandate name, SECID, type, geographic focus, vintage year, domestic currency, total commitment, and our ownership percent within the mandate
· Required investment information: company names, mandate ownership percent, sector, geography, date acquired, equity owned, realized proceeds, current fair value of the company, and date last updated
· Required transaction information: transaction date, transaction amount, and a description of the transaction
· Required quarterly report information: quarter and year of the report, date report was received, current value of the fund, cumulative commitments, unfunded commitment, quarterly and cumulative capital calls, and quarterly and cumulative distributions
· Required foreign exchange rate information: date, USD to CAD, EUR to CAD, GBP to CAD, etc.
· Required benchmark returns: date, S&P500, MSCIWorld, MSCIEuro, MSCIEAFE, etc.
The relationships are as follows:
· Each fund can have any number of mandates, but each mandate belongs to only one fund
· Each mandate will have a mandate report each quarter, and will have at any given time 0-50 investments.
I’ve attached a photo of the database structure I have come up with. Please take a look and see if there is any glaring issues or improvements you think I could make.
Thank you in advance!