Solved Help setting up a new Investment Portfolio database using normalization

[mike60smart]

It might help if you can give us an example in Excel

 

[mike60smart]

Also the attached table is all wrong.

All of the fields in this table should be Values in a Table.

The table Structure would be as follows:-

tblTypes
-TypeID - PK - Autonumber
-Type - Text

 

[Pat Hartman]

it's just that nagging thing in my head because I know there's a much better way to do it.

We are all entitled to our opinions

 

[HalloweenWeed]

OK, I think it might help to show my actual transactions that I need to enter in this database, I will just edit the personal info out. Please remember, one of my goals is to also reduce the number of entries necessary for each transaction (obvious on my Broker's activity page) by nearby transactions of the same date), by making a form that allows tax & fee entries simultaneously to the security sale (and I can't say these will never happen on a purchase either). Type "ForeignTax" is automatically set (by a checkbox) at the time the (security) account is made, so far only "ETF"'s have "ForeignTax," but I don't know that this will always be true. "ETF"'s have some slight rule changes in regard to trading.

My records to transcribe, and a sample of the way they are shown by the Broker are below. As you can see, the Broker's transactions are exactly like bookkeeping, but not very helpful when trying to glean statistics. The Broker has statistics of course, but it has issues, such as I cannot see the statistics of my gain/loss for a particular stock after I have sold all of it. There are records, but they lack statistics. Not only that, if the Broker ever goes bankrupt or gets hacked (such as encryption hack), who knows if I will ever have access to this information again, same goes for me closing my account at the Broker. So I want to keep it for myself - and remember, this is not my only motivation for this database.

 

[HalloweenWeed]

What I am trying to do, above all else, is get a total Gain for total investments, each stock invested in, and be able to break that down into timeframes too. This is the real measure of how one does in investments. Gain is not just the price sold minus the cost of the shares, it's also the dividends (or interest) added, minus the taxes and fees. The Broker's charts do this too, for my total investments, but like I said earlier they don't break this down into each investment, where I have already sold all my stock. Perhaps they would if I paid for a premium subscription... Then I want to be able to graph these in Excel (makes superior charts), possibly along with or overlayed with the performance of indices such as S&P 500 (optional). I make some pretty sharp-looking, ergonomic, and very informational (Excel) charts in my work - they convey the bigger picture (and any trends of course) rather well.


I also need to reconcile the foreign taxes from each account annually and match it to my tax documents provided by the Broker, not to mention I should be able to calculate this several weeks before I receive the tax documents (in the snail-mail) from the Broker (they are not available online, they only mail them!). I also want the ability to show things like dividends paid from each account, minus taxes, per given timeframe.

I want to be able to quickly see how much the Foreign taxes have affected my return of investments, per account. This helps me evaluate the effectiveness of investments in foreign ETF's regardless of that stock's actual performance. They not only tax on sale, but tax the dividends as well.

 

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