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.
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.
