Solved Help setting up a new Investment Portfolio database using normalization

[plog]

From what you've provided it looks like you've overly complicated your table structure. From the sample data you provided in the .pdf it looks like you just want a database to track transactions. Then with that data determine profit/loss.

That .pdf is the input data that you need to accomodate. So, let's take one entry from it and sketch out the tables you need to accomodate it:

1/19/22 Sold 3.0 (all) shares of ATVI Activision Blizzard for $246.02 @ $82.0101
Cost: $175.95 - $0.01 fee; Profit: $70.06

We can disregard the second line ("Cost: $175.95 ...") because that data doesn't need to be input because it should be calculable with other data that will be in your tables. So, from the first line here's the tables and fields I see:

tblStock
stock_ID, autonumber, primary key
stock_Symbol, text, will hold stock symbol (e.g. ATVI)
stock_Name, text, will hold full name of stocks (e.g. Activision Blizzard)

tblTransTypes
tt_ID, atuonumber, primary key
tt_Name, text, name of transaction (buy, sell, fee, dividend, etc)
tt_Value, number, determines how transaction effects total (1 is credit, -1 is debit, 0 doesn't effect total)
tt_PerShare, Yes/No, determines if this transaction is multiplied per share or just applied by itself

tblTransactions
trans_ID, autonumber, primary key
ID_stock, number, foreign key to tblStock to determine what stock is being transacted
ID_TransType, number, foreign key to tblTransTypes to determine how transaction effects total
trans_Time, date/time, date/time of transaction
trans_SharePrice, number, price per share of stock being transacted (e.g. 82.0101)
trans_Shares, number, number of shares transacted (e.g. 3)

Those tables should accomodate all the data in your .pdf file that will be input into the database. If not, provide me an example and I will either explain how those tables do, or correct them so that they will.

 

[HalloweenWeed]

From what you've provided ...

tblStock
stock_ID, autonumber, primary key
stock_Symbol, text, will hold stock symbol (e.g. ATVI)
stock_Name, text, will hold full name of stocks (e.g. Activision Blizzard)
...

OK, I'll consider that if you tell me what I would do for my first transaction: Deposit $500 in cash acct. Would that be in tblStock?

 

[mike60smart]

OK, I'll consider that if you tell me what I would do for my first transaction: Deposit $500 in cash acct. Would that be in tblStock?

No It would go in the tblTransactions.

On your Data Input Form based on tblTransactions you would create a combobox to Lookup the Stock value from tblStock
and another Combobox to Lookup the Type of Transaction from tblTransTypes.

 

[jdraw]

HalloweenWeed,

I have not reviewed each and every post in this thread, but I do have an observation that you might consider.
We're 48 posts in and several responders have questioned your table structure. Your initial concerns were with subforms and reports. Most database issues are a direct result of poor set up of tables and relationships. They either don't fit the business, or they don't use the database system as it was intended. You have been advised to review Normalization. Some have suggested you have data in field names.

My concern is that you will be in constant work around mode if you don't act on plog's comments #37. You should be able to work with an evolving data model/relationships window and some sample data transactions and process the data against the model. Building the model is like building a blueprint for a building. You can do a lot of "what ifs' before you start physical construction. Getting your database blueprint designed to match your requirements is critical. Some say 80+% of database failures/shortcomings can be traced back to structure/design of the tables and relationships.

I think it's time to step back and review your design based on the comments you have received.
See stump the model
Good luck.

 

[plog]

OK, I'll consider that if you tell me what I would do for my first transaction: Deposit $500 in cash acct. Would that be in tblStock?

To do that you would add a record in tblStock to accommodate your Cash account and a record in tblTransTypes to handle cash deposits into it. Then for the actual deposit you would add an entry to tblTransactions using the Cash ID_Stock value, the deposit ID_TransType.

A wrinkle to having this Cash acct is that you now must either fix your workflow or your tables to accomodate that. There are 2 ways:

1. Double transactions. Your tables stay the same, but now for every stock transaction record that effects the cash acct (buy/sell) you must make another transaction record for the Cash Acct (deposit/withdrawal).

2. Add a Cash Acct flag to tblTransactions or add one to tblTransTypes. That way you can add just 1 transaction record for a stock sale/purchase and that flag will let you know the transaction effects your Cash Acct and your queries can be built so that it correctly calculates the Cash acct balance.

This gets more complicated with multiple Cash Accts or if possible for a sale/purchase to only some times use the Cash Acct.

 

[HalloweenWeed]

...

Thank you @jdraw, that's exactly what I have been doing (exc. maybe that referenced thread). I've reviewed normalization and it's levels 1-4 (I forget at the moment where I did this). And @Pat Hartman, @plog, and @mike60smart have been very patient with me and helpful here, kudos for the help. I am simply trying to learn now to implement this into my requirements ATM. I'm going slowly right now because the past several days showed me that if I get too far ahead I will not set it up right. We are working on basic structure right now.

EDIT 8/1/23: It was Roger's Access Blog. I studied parts 1-5 (all).

 

[HalloweenWeed]

2. Add a Cash Acct flag to tblTransactions or add one to tblTransTypes. That way you can add just 1 transaction record for a stock sale/purchase and that flag will let you know the transaction effects your Cash Acct and your queries can be built so that it correctly calculates the Cash acct balance.

This gets more complicated with multiple Cash Accts or if possible for a sale/purchase to only some times use the Cash Acct.

THIS. Yes I like that option (2). I can simply ignore possible different cash accounts, all that matters is the cash balance dividends/interest accrued on them and I can treat them as if there are only one for the sake of simplicity, no problem. Actually, I was thinking flag for withdrawal, flag for deposit, but it's clear to me now that your single flag says the same thing (thus normalization). I will work on this more soon. Thank you

 

[HalloweenWeed]

I will drop the debit & credit accounting transaction method request/specification. It was only for purposes of validation/error checking. I think that with the methods we will come up with it won't be necessary, and all I have to do anyway is reconcile it with the Broker's records.

 

[HalloweenWeed]

Does somebody have a recommendable larger checkbox than Access standard? (Link or text to google please.)

 

[MajP]

Not sure why this runs so slow. But demos wingdings on continuous form. If on single form view then you can do fancy things with images.

 

[HalloweenWeed]

Good morning my appreciated Access Senseis,
Well I have removed the complained-about fields. I can always easily add them in in the final build.
Here's what I have now:

Does this look good? I will start working on the forms and related queries.

EDIT: fixed leading character case: frmAcctTypAddEdit.

 

[mike60smart]

Good morning my appreciated Access Senseis,
Well I have removed the complained-about fields. I can always easily add them in in the final build.
Here's what I have now:
View attachment 109216

Does this look good? I will start working on the forms and related queries.

EDIT: fixed leading character case: frmAcctTypAddEdit.

Would query the Joins?
I may be wrong but Normally it is a 1 to Many and Option Type 1 in the Relationship Window.
You would normally set the type of Relationships you have in your queries.

 

[plog]

Nope, still not it. I just don't know how to impress upon you this point any further:

You should not use values as names. Table/Field names should not be terms specific to your data. Names should be so generic anyone familiar with the english language should be able to tell what is in them.

Your transaction table still has values as names. It should have a lot less fields and all those values should go in as value in records, not as field names in that table. Your transaction table should be closer to this:

tblTransTypes
tt_ID, atuonumber, primary key
tt_Name, text, name of transaction (buy, sell, fee, dividend, etc)
tt_Value, number, determines how transaction effects total (1 is credit, -1 is debit, 0 doesn't effect total)
tt_PerShare, Yes/No, determines if this transaction is multiplied per share or just applied by itself

Than the tblXactntyp table you have.

 

[mike60smart]

Good morning my appreciated Access Senseis,
Well I have removed the complained-about fields. I can always easily add them in in the final build.
Here's what I have now:
View attachment 109216

Does this look good? I will start working on the forms and related queries.

EDIT: fixed leading character case: frmAcctTypAddEdit.

In the attached I reworked the Account types so that the table tblAccountTypes contains the List of Types.

 

[HalloweenWeed]

In the attached I reworked the Account types so that the table tblAccountTypes contains the List of Types.

I looked at what you did, and I think you don't understand, @mike60smart, ETF, Foreign tax, & Money Market are not mutually exclusive nor dependent on one another. They are individual independent binary states. As far as I can guess, every combination of the 3 (all 8 possibilities) are possible, but as far as I know there may only be 2-3 possibilities. As I cannot saddle my data with such limitations, leaving them separate allows for every possibility. As far as I know, there could be more possibilities unknown to me now to cover, I cannot predict everything I might need the future, I just know I need these.

Specifically, a "core class" account is the only account type that can store cash. Therefore, before allowing any monetary transaction a "core class" account must be selected, that's easy, just use that account type. But many transactions are independent of ETF, Foreign tax, & Money Market designations. 2 of these 3 aren't even important now, I have them in there because these accounts may be handled differently in certain situations, I want them in there because they could be important later when I add a functionality to this database. Really, I don't even want to have types 2&3 there in your table, but simply binary yes/no's to tell my forms and reports how to behave, such as whether or not a tax on a transaction is a foreign tax. As a matter of fact, I also need a field to denote the foreign entity of the tax, perhaps a Country, or a Region, just to reconcile the taxes paid with the US Federal Income Tax return documents. That doesn't need a separate account type as far as I knew originally, but I understand where you guys are coming from with this desire to place it in the account type table. So I go along with it, I know I am in a learning process.

 

[mike60smart]

I looked at what you did, and I think you don't understand, @mike60smart, ETF, Foreign tax, & Money Market are not mutually exclusive nor dependent on one another. They are individual independent binary states. As far as I can guess, every combination of the 3 (all 8 possibilities) are possible, but as far as I know there may only be 2-3 possibilities. As I cannot saddle my data with such limitations, leaving them separate allows for every possibility. As far as I know, there could be more possibilities unknown to me now to cover, I cannot predict everything I might need the future, I just know I need these.

Specifically, a "core class" account is the only account type that can store cash. Therefore, before allowing any monetary transaction a "core class" account must be selected, that's easy, just use that account type. But many transactions are independent of ETF, Foreign tax, & Money Market designations. 2 of these 3 aren't even important now, I have them in there because these accounts may be handled differently in certain situations, I want them in there because they could be important later when I add a functionality to this database. Really, I don't even want to have types 2&3 there in your table, but simply binary yes/no's to tell my forms and reports how to behave, such as whether or not a tax on a transaction is a foreign tax. As a matter of fact, I also need a field to denote the foreign entity of the tax, perhaps a Country, or a Region, just to reconcile the taxes paid with the US Federal Income Tax return documents. That doesn't need a separate account type as far as I knew originally, but I understand where you guys are coming from with this desire to place it in the account type table. So I go along with it, I know I am in a learning process.

So are you saying that the tblAccDirectory could have 1 or more of the Account types associated with it?

If this is the case then it would be managed differently.

Here with an example in the attached.

 

[HalloweenWeed]

So are you saying that the tblAccDirectory could have 1 or more of the Account types associated with it?

If this is the case then it would be managed differently.

No, I'm saying that as far as I know now:
All ETF accounts are foreign taxed. Just because I think so, not positive.
There may be non-ETF's with foreign tax.
Money Market has nothing to do with whether or not it is an ETF, or has foreign tax.
I don't know whether or not any non-ETF's will have foreign tax, it's beyond my knowledge.
I may not know when an account is foreign taxed when I create the account. I only know after I receive the end-of-year tax documents.

So, each one is just there as a separate property to the account. You guys are just making this to difficult, it's not that difficult to just have those binaries there. This is why I wanted it in the account directory, not in the account type table.

 

[mike60smart]

No, I'm saying that as far as I know now:
All ETF accounts are foreign taxed. Just because I think so, not positive.
There may be non-ETF's with foreign tax.
Money Market has nothing to do with whether or not it is an ETF, or has foreign tax.
I don't know whether or not any non-ETF's will have foreign tax, it's beyond my knowledge.
I may not know when an account is foreign taxed when I create the account. I only know after I receive the end-of-year tax documents.

So, each one is just there as a separate property to the account. You guys are just making this to difficult, it's not that difficult to just have those binaries there. This is why I wanted it in the account directory, not in the account type table.

OK so put it another way.
If you have fields in a table for specific TAX Types and in the future there is a need to add additional TAX Types it means
you have to Modify the Table, any related queries, Forms and Reports.

This is why a List of Tax Types should always be in a separate table.

Up to you if you want to ignore the advice.

 

[HalloweenWeed]

OK so put it another way.
If you have fields in a table for specific TAX Types and in the future there is a need to add additional TAX Types it means
you have to Modify the Table, any related queries, Forms and Reports.

This is why a List of Tax Types should always be in a separate table.

Up to you if you want to ignore the advice.

Well, I suppose it could be a "tax entity" field: 0 = No tax, 1 = entity 1, etc. I could work with that. Could that be in the Account Directory, instead of the account type table?

 

[HalloweenWeed]

The four "cash" fields are still suspect. This looks like a repeating group. Can ALL of the values be true at once or can only one be true? If only one can be true, there should be only a single field and that field would contain one of the four value types rather than having four fields with true/false values, you have one field with one of four values.

OK, now I think I understand what plog was trying to convey, that's exactly it. All these are really descriptors of the transaction type, and your answer is definitely no, they were there for me to use to either poll for the working of my forms, and/or to quickly see myself what the transaction type ID was for a particular transaction when programming/building the forms and reports. So, by you guy's definition of not part of a normalized database they need to go. But for me, that's like taking a jump without a safety net. I will remove them, but I have little confidence that I can build this correctly afterward.

And this has helped me understand @plog's post:

Nope, still not it. I just don't know how to impress upon you this point any further:

You should not use values as names. Table/Field names should not be terms specific to your data. Names should be so generic anyone familiar with the english language should be able to tell what is in them.

Your transaction table still has values as names. It should have a lot less fields and all those values should go in as value in records, not as field names in that table. Your transaction table should be closer to this:

tblTransTypes
tt_ID, atuonumber, primary key
tt_Name, text, name of transaction (buy, sell, fee, dividend, etc)
tt_Value, number, determines how transaction effects total (1 is credit, -1 is debit, 0 doesn't effect total)
tt_PerShare, Yes/No, determines if this transaction is multiplied per share or just applied by itself

Than the tblXactntyp table you have.

OK I'll give it another shot.