Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 01-14-2011, 05:04 AM   #1
Newly Registered User
Join Date: Nov 2009
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Bob is on a distinguished road
design of investment portfolio database

I am building an investment portfolio database (another one, yes I know)

I am currently analysing the transactions ie. Buys and sells of shares (& bonds) plus interest and dividend payments. There are more types of transactions but letís leave that for the moment.

All transactions have a number of fields in common plus a unique reference PK (implemented as an autonumber field). I therefore have made a design with a transaction header table (tblTransactionHeader) with a one-to-one relation to the transaction trade table (tblTransactionTrade). Fields in the tblTransactionTrade are unique to the nature of buying and selling securities.
Equally I have a table with details about interest and dividend payments (tblTransactionInterestDividend) with fields unique to this type of transaction. This table also have a one-to-one relation to the tblTransactionHeader table.

So far so good.

PK on the header table is an autonumber field, while PK on both trade table and interest tables are a long integer. I created a one-to-one relationship from the header table to PK on the other tables.

I thought this could work until I starting building a form for the trade. Iíve tried a couple of approaches without success and the problem boils down to the following.

When moving from the header record (tblTransactionHeader) to the trade part (tblTransactionTrade) Access needs to save the record. However, it cannot be saved when thereís no existing related record in tblTransactionTrade. Record on tblTransactionTrade cannot be saved either because some fields are compulsory. Iím stuck!!!

I could off course do without the forced relationship on the database and control the related records on the form, but I have a feeling this is not a nice solution. Keen to hear your thoughts.

Note. The term ďtransactionĒ is not used here in the database terminology as a set of actions to be regarded as one unit, but rather, it is the common term for buying, selling and other portfolio actions.

Bob is offline   Reply With Quote

header table , investment , one-to-one , portfolio , transaction

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling Stock Investment Data from Web into Access Table HMikeWenzel Tables 6 01-19-2017 03:58 AM
Creating Time Series for Portfolio Values JapanFreak Queries 4 09-04-2007 10:53 PM
Stock Market Portfolio database sample Keith Nichols General 0 09-09-2006 02:39 AM
[SOLVED] Date to Date performance calculation for investment data j77foster Queries 0 05-06-2005 01:31 PM
Database Design Busto General 3 12-06-2002 05:52 PM

All times are GMT -8. The time now is 03:23 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World