Solved Help setting up a new Investment Portfolio database using normalization

[mike60smart]

Sorry but it seems to be a lot of work when all you need is a Combobox to choose a specific Transaction Type from a List

 

[HalloweenWeed]

I also have a version of that which searches as-you-type for INSTR() matches (without using that function, using "LIKE"), or as Access calls it: MID(). This search (pick list) method works on however many fields you choose, for instance Last, First, Nickname. Type any name, and you are presented with all the partial matches. This is in-use where I work.

 

[Gasman]

Plus nifty people like @MajP have created classes for Find As You Type for combos etc.

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

 

[HalloweenWeed]

Plus nifty people like @MajP have created classes for Find As You Type for combos etc.

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

Like you said, that's 'nifty.' I will try that after I get it working with combo boxes, after the otherwise final build. I am open to using comboboxes for the initial build(s).
Thanx @MajP! Much appreciated. I will have to figure out how to use that at a later date.

 

[HalloweenWeed]

OK, you don't understand how they work so you are constantly fighting with them...

No Pat, I understand, it's just that nagging thing in my head because I know there's a much better way to do it. I use my extra work time to make improvements such as this to existing work db's. Probably will stick to MajP's solution from now on, I'm sure it's easier to troubleshoot. That said, I do really like the use of a Queryable Access table for the result in my solution, when using vba for the basic form functionality. However, I know this is not even nearly the fastest solution and therefore not the RAD solution. This database that I am working on is obviously not for my official work (except the training experience it gives me), and therefore I am a bit more picky about the precise functionality - of the final build. That said, since I am learning, I will do the quickest/easiest/simplest methods (what you guys recommend) first and then improve it the way I would like later. However, I reserve the right to bitch (a little) about Access LOL

 

[HalloweenWeed]

I didn't know: when you try to create a second relationship between two tables, and the dialog box pops up saying "do you want to" edit the original relationship, you can still add another relationship. I previously just cancelled out of that, thinking that it was going to disallow one of the relationships. I just now made the second relationship work for the first time, FYI. But, a new box with a table popped up, with a "_1" suffix, but there was no new table in the tables list. I've seen this before in another database, but didn't understand where it was coming from (I thought it was a "ghost" table, unexplained to me). Here's what I have now (database attached, you may browse table fields):

Now I'm going to build an account type form, to generate proper account types (populate tblAcctTyp).

Edited typo 8/1/23.

 

[MajP]

Like you said, that's 'nifty.' I will try that after I get it working with combo boxes, after the otherwise final build. I am open to using comboboxes for the initial build(s).
Thanx @MajP! Much appreciated. I will have to figure out how to use that at a later date.

Not sure if you noticed in that FAYT example there are examples for combos, listboxes, and forms. In the form demo you can do an FAYT on any field you want (just have to add a field selector)

 

[HalloweenWeed]

Not sure if...

Yes, I did spend about 20 minutes looking at it, looked at the default form and one of the class modules. Tried the comboboxes. Nice to know you are still around should I have a great deal of difficulty understanding something. Again, Kudos @MajP

 

[mike60smart]

I didn't know: when you try to create a second relationship between two tables, and the dialog box pops up saying "do you want to" edit the original relationship, you can still ad another relationship. I previously just cancelled out of that, thinking that it was going to disallow one of the relationships. I just now made the second relationship work for the first time, FYI. But, a new box with a table popped up, with a "_1" suffix, but there was no new table in the tables list. I've seen this before in another database, but didn't understand where it was coming from (I thought it was a "ghost" table, unexplained to me). Here's what I have now (database attached, you may browse table fields):
View attachment 109093

Now I'm going to build an account type form, to generate proper account types (populate tblAcctTyp).

Hi
All of the tables in your Relationship Diagram with the Suffix's are just the same table with an Alias of _1, _2 & _3

 

[HalloweenWeed]

Hi
All of the tables in your Relationship Diagram with the Suffix's are just the same table with an Alias of _1, _2 & _3

Yeah, I picked up on that, but not sure if you want me to do something with that info. Access did that automatically, so I'm assuming that's OK.

 

[mike60smart]

Yeah, I picked up on that, but not sure if you want me to do something with that info. Access did that automatically, so I'm assuming that's OK.

Which values are you going to be storing in tblXactn that are currently in tblAcctDirectory?

 

[HalloweenWeed]

Which values are you going to be storing in tblXactn that are currently in tblAcctDirectory?

None.
There are up to 4 accounts that get affected by each transaction, thus each record in the transaction table. Thus my earlier question on thread page 1 about how to do that in the forms. I'm trying very hard to avoid having to do multiple form submissions for each transaction, which can lead to accounting errors due to entry error. Each transaction has a debit account (where the money goes to), a credit account (where the money comes from), sometime a tax payment that can be foreign tax, and sometimes a Broker fee. Each of these need to be tracked in an account. And I need to be able to reconcile my "foreign taxes paid" annually with my US annual tax return submission, broken down into amounts for each foreign entity (country).

 

[plog]

I've skimmed and disregaded all the posts about forms and combo boxes and have just focused on the latest screenshot and database sample you posted (post #30). With that I see you have some real issues with your tables and any form talk is irrelevant at this point. Here's what I see with your table structure:

1. Circular relationship. tblXactn, tblBroker and tblAcctDirectory have a wrong relationship--I don't know enough about your data to know which one. You should only be able to trace 1 path between tables--I can trace 2 between tblBroker and tblXactn (directly and also via tblAcctDirectory). What you are saying is that an Xactn can have a broker, but it can also have an AcctDirectory that may be a different broker. Is that true? Can 2 different brokers be associated with an Xactn?

2. Storing duplicate data. tblAcctDirectory has an AcctActiveDate and an AcctActiveTimeStamp--what's the difference between these 2 fields? Could an AcctActiveDate ever be a different date than what is in AcctActiveTimeStamp? If not, then you only need AcctActiveTimeStamp. Same for ActInactive fields.

3. Storing calculated data. You shouldn't store data that can be calculated from other data in your database. In addition to those AcctActive/Inactive fields tblAcctDirectory also has a Yes/No AcctActive field. You don't need AcctActive because you can look at your Active/Inactive date fields and determine that.

And here's the biggie:

4. Using field names to store data. This is what I guessed about your data in my first post--you are using values as names. tblAcctType should not have a field named for each Acct Type but each account type should just be a value in a field. Same for tblXactnTyp and tblXactn.

Back to the car analogy. When a used car lot has a database of the cars it sold it has tblSales with a field for CarMake and in that CarMake field that put Honda, Toyota, Ford, Buick, etc. They do not link tblSales to tblCarMakes in which they have a bunch of fields named after every make they offer (a Honda Yes/No, a Toyota Yes/No, a Ford Yes/No, etc. etc.). They simply store the make in the sales table with the value. With your database you have chosen the second and wrong method.

A database isn't a spreadsheet--you don't just throw more categories across the top. A database should accomodate data vertically (with more rows) and not horizontally (with more columns). Suppose you get a new tblAcctTyp. In your system you must add a new field to the table, reconfigure your form to accomodate it, reconfigure any query and report as well. In a properly structured database (like the car analogy) you simply add that new value to the available options and nothing else needs to be done.

 

[HalloweenWeed]

I've skimmed and disregaded all the posts about forms and combo boxes and have just focused on the latest screenshot and database sample you posted (post #30). With that I see you have some real issues with your tables and any form talk is irrelevant at this point. Here's what I see with your table structure:

1. Circular relationship. tblXactn, tblBroker and tblAcctDirectory have a wrong relationship--I don't know enough about your data to know which one. You should only be able to trace 1 path between tables--I can trace 2 between tblBroker and tblXactn (directly and also via tblAcctDirectory). What you are saying is that an Xactn can have a broker, but it can also have an AcctDirectory that may be a different broker. Is that true? Can 2 different brokers be associated with an Xactn?

2. Storing duplicate data. tblAcctDirectory has an AcctActiveDate and an AcctActiveTimeStamp--what's the difference between these 2 fields? Could an AcctActiveDate ever be a different date than what is in AcctActiveTimeStamp? If not, then you only need AcctActiveTimeStamp. Same for ActInactive fields.

3. Storing calculated data. You shouldn't store data that can be calculated from other data in your database. In addition to those AcctActive/Inactive fields tblAcctDirectory also has a Yes/No AcctActive field. You don't need AcctActive because you can look at your Active/Inactive date fields and determine that.

And here's the biggie:

4. Using field names to store data. This is what I guessed about your data in my first post--you are using values as names. tblAcctType should not have a field named for each Acct Type but each account type should just be a value in a field. Same for tblXactnTyp and tblXactn.

Back to the car analogy. When a used car lot has a database of the cars it sold it has tblSales with a field for CarMake and in that CarMake field that put Honda, Toyota, Ford, Buick, etc. They do not link tblSales to tblCarMakes in which they have a bunch of fields named after every make they offer (a Honda Yes/No, a Toyota Yes/No, a Ford Yes/No, etc. etc.). They simply store the make in the sales table with the value. With your database you have chosen the second and wrong method.

A database isn't a spreadsheet--you don't just throw more categories across the top. A database should accomodate data vertically (with more rows) and not horizontally (with more columns). Suppose you get a new tblAcctTyp. In your system you must add a new field to the table, reconfigure your form to accomodate it, reconfigure any query and report as well. In a properly structured database (like the car analogy) you simply add that new value to the available options and nothing else needs to be done.

Please understand: sometimes I just add a field (EDIT: that I delete on the final version) to make troubleshooting and sleuthing easier.
1. Yes, I see your point and have corrected it, thank you. That was my confusion as a result of converting from a malformed database. Since each account has an assigned Broker, then it is not needed in the transaction table. That said, transactions are done by the broker, so accounts are selected based on the broker selected at the beginning of entering transactions.

2. Each transaction has occurred before these records are entered, and in this case many transactions are years ago. It is necessary to have the date of the actual transaction to match the entry to the records I already have. The "TimeStamp" is a Now() timestamp of the moment the record was created in the database. Just for historical record - I just want it there. Should another person ever enter records, I would also add a username to that, similar to what we use at work, which may complicate matters more. I want this record to track when the accounts were made as well.

3. As far as the Active/Inactive: this is really only used for the "Core class" account which is default cash storage in the Broker's account. You see, when a cash deposit is made, it first goes to a "Cash" account, then the Broker automatically moves it to the "Core class" account. This account can vary, as (some?) Brokers give you a choice of "Core class" accounts, with differing features. This is where your cash resides while waiting to be invested, and where the remainder of your cash resides. When you change accounts, the previous becomes inactive (after the transfer of all funds within), and that is not usually the date on the timestamp. This database will keep a record of which "Core class" account was used and when. There are also features like dividends on the "Core class" account to be recorded, and stats to be compiled. I want the forms to autopopulate with the "Core class" account for all securities trades, using the AcctActive binary, I think it's a lot easier to code than what you're asking for. I'm not even sure I need the dates there, but I'm going to put them there for now, I can always delete the field later if I don't need it.

I am also mulling over whether to drop using the "cash" account in the database. For all practical purposes, the deposits, withdrawals, and other cash transactions could come directly from the (active) "Core class" account. What do you all think?


4. I have already done this, I had trouble making a proper form for making the account type records in tblAcctTyp. I settled on 7 account types and 3 fields to denote the 3-4 variations of them. I now have a table of 10 total account types, 10 records:

 

[HalloweenWeed]

Do you think I should add a table for the core class active/inactive date & timestamps? I designed it this way because I don't see any reason to keep the date/timestamps past the last change for each of active/inactive. Each time one or the other is written, I don't think there's a reason not to overwrite the last date/timestamp.

 

[plog]

1. Good.

2. What? Your explanation makes it sound like AcctActiveTimeStamp has nothing to do with AcctActive but just tells when the record was add/edited? If so, why do you have 2 fields for this? If not, please explain better.

3. Again, what? I don't understand your process, I just understand databases and AcctActive doesn't make sense if you already have 4 other fields that relate to if a record is Active/Inactive. Suppose you didn't have this field, could you look at the other 4 and determine if a record should be AcctActive=True or AcctActive=False?

4. You did not do this in the most recent database you uploaded. Still tons of fields that shouldn't exist and instead be values inside a more generically named field.

5. I don't understand your data nor the real life system/process it tracks to make recommendations. For that you are going to have to start from scratch and give me 2 paragraphs answering these questions:

A. What does your organization do? Pretend its career day at an elementary school and explain to me like I'm a kid without any frame of reference. Don't use any database jargon or get too specific, just an overview of what it is you do.

B. What does/will this database allow you to do in that capacity? Minimal database jargon allowed. Tell me what this database should help you do.

 

[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

 

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