Solved Help setting up a new Investment Portfolio database using normalization

[LarryE]

You should use tiny subforms. All you need to do is to enter an amount, which you have to enter no matter what.


No, that is not correct.

Bound forms handle the problem just fine. If you don't dirty the subform, no record would be created. If you dirty it by entering a tax or fee amt, Access automagically populates the foreign key provided you have the master/child links set correctly.

In the vast majority of forms, the only VBA code you ever need is code in the form's BeforeUpdate event to validate data. Access takes care of pretty much everything else all by itself. You don't need navigation code, although some people like to use custom buttons because that is build in. You don't need save code because that is built in. The code you actually need is the code to prevent saving invalid data. For example, have you ever seen a date like 2/12/203? It happens more frequently than you might imagine. The date is perfectly valid as far as the date data type is concerned. It is just illogical unless you are working with ancient event data but it is a common type of typo that your validation code should catch because the date is out of what would be the logical range for any date in most applications.

May I just butt in here and ask a question? I have been following this because I have built an ACCESS app for this purpose in the past.

If any single transaction can have MORE THAN ONE taxing authority tax amount, then you probably need a separate tax table linked to the transaction table with a TaxID Primary Key and TransactionID as a Foreign Key. So if any transaction can have say U.S. Income Tax affects AND a Foreign tax affect AND/OR maybe a state tax affect, then a tax table is warranted. If not, then a taxing authority name field and amount field (and maybe percentage) could be kept in the transaction table.

 

[HalloweenWeed]

May I just butt in here and ask a question? I have been following this because I have built an ACCESS app for this purpose in the past.

If any single transaction can have MORE THAN ONE taxing authority tax amount, then you probably need a separate tax table linked to the transaction table with a TaxID Primary Key and TransactionID as a Foreign Key. So if any transaction can have say U.S. Income Tax affects AND a Foreign tax affect AND/OR maybe a state tax affect, then a tax table is warranted. If not, then a taxing authority name field and amount field (and maybe percentage) could be kept in the transaction table.

Thank you @LarryE . The answer is no, only one.

 

[HalloweenWeed]

OK, thank you @Pat Hartman . That said, I feel more comfortable with the fields in the transaction table, I feel more confident that I will be able to do what I want with less complications/complexity. Like you said, not making millions of records here (probably just a few hundred in my lifetime).

Here's what I have now, I have eliminated the tax, fees, and split table and inserted those fields into the transaction table.
I have added a 'default settings' table, as that table sets the presently used Broker. To change Brokers, one needs to close all forms/reports and set dflt_BrokerID (presently can be done manually with the 'add/edit Broker Form).
I have made some early-build forms and made some records, but no transactions yet, I haven't even started on the transaction form yet.
I also swapped the direction of the tables in the relationship graphic, makes more sense to me.

 

[Pat Hartman]

I think I mentioned the possibility a US tax/fee (not clear which) in the future Larry if the White House gets its way.

 

[HalloweenWeed]

I think I mentioned the possibility a US tax/fee (not clear which) in the future Larry if the White House gets its way.

Yeah, there is a "Domestic" (default) entry in the tax entity table. TY
Also, I think it would probably be on my Income tax return, not in these transactions.

 

[Pat Hartman]

@HalloweenWeed I guess I never mentioned how annoying prefixes on column names in tables are once you start making queries and writing code. WHY - in the case of queries, all you will see are the prefixes. You will have to constantly expand the column widths to get to any meaningful part of the name. As for code, Intellisense won't kick in until you get past the prefix so you are dramatically reducing its usefulness.

Prefixes are not wrong per se. They are just too annoying for words. If you insist on the concept, suffixes will be much less in your way and do the same thing.

 

[HalloweenWeed]

@HalloweenWeed I guess I never mentioned how annoying prefixes on column names in tables are once you start making queries and writing code. WHY - in the case of queries, all you will see are the prefixes. You will have to constantly expand the column widths to get to any meaningful part of the name. As for code, Intellisense won't kick in until you get past the prefix so you are dramatically reducing its usefulness.

Prefixes are not wrong per se. They are just too annoying for words. If you insist on the concept, suffixes will be much less in your way and do the same thing.

Oh thank God (not literally). I was just doing it because plog showed it. I will remove them.

 

[Pat Hartman]

 

[LarryE]

OK. I use a very simple table structure like this:

1. Each Financial Organization can have multiple accounts (IRA, Investment, Checking Account etc.)
2. Each Account may contain multiple Products ( Cash, Bond, Stock, Mutual Fund etc.)
3. Each Product may have multiple transactions

I also keep a running balance in the transactions table of Product Balances. Now normally, you would not keep calculated values in a table. However, in the case of investment portfolios, people do want to keep a balance history and the dates of those balances. At least I do.

 

[LarryE]

I think I mentioned the possibility a US tax/fee (not clear which) in the future Larry if the White House gets its way.

Don't even get me started.

 

[HalloweenWeed]

OK. I use a very simple table structure like this:
View attachment 109261

1. Each Financial Organization can have multiple accounts (IRA, Investment, Checking Account etc.)
2. Each Account may contain multiple Products ( Cash, Bond, Stock, Mutual Fund etc.)
3. Each Product may have multiple transactions

I also keep a running balance in the transactions table of Product Balances. Now normally, you would not keep calculated values in a table. However, in the case of investment portfolios, people do want to keep a balance history and the dates of those balances. At least I do.

Thank you for that @LarryE
That's a bit more complicated than I want to get. I keep my retirement portfolio separate. This is just for my stock market forays. I may at some time put some money from this coffer into a mutual fund, and that's why I need it in the account types. But as far as I can imagine, I'm not going to want to keep separate track of that other stuff in this database. It is just a tool to help my upcoming investments, and for tax return forms for these foreign taxes. Please, if you beg to differ I would be interested in what you have to say about that. Thank you.

 

[HalloweenWeed]

Hello guys,
I've done some forms and accounts, working on building up to my first entry, haven't finished my transaction form yet, and considering using a separate form for my cash deposits/withdrawals. I've added a 'Cash account' field to my transactions table, due to the need, and when I went to create that link to the account directory I experienced a slight hitch in the relationship linking (after a similar hitch in the relationship between the new 'default cash account' setting in the Broker table). So I'm going to run this by you guys, is this right?:

Remember, the "cash account" a.k.a. "core class" account, changes over time. You cannot recreate the proper accounting if there isn't a record of what it's setting was at the time of the transaction. For each security transaction that involves cash, the cash or security is subtracted from one account and cash or security is added in another. And I don't want to do double-entries of each transaction.

The "default cash account" is unique to each Broker. If the broker changes, so does the "default cash account." So far, the only way to change the Broker, is to change the "default broker" setting in tblDfltSetngs via the Broker form "frmBrokerAddEdit." Should I see the need, I could add the functionality in my upcoming "main menu" form.

Please let me know what you think. Meanwhile, I'm going to work on my (securities?) transaction form. Thank you.

EDITED: misperception - "cash or security" (Cash or shares).

 

[HalloweenWeed]

I'm trying to decide whether to include my "cash transactions" (deposits/withdrawals) in my (securities/main) transaction form. Cash transactions are the only transactions that don't both add an amount in one account, and subtract from another. It might be tricky to adjust the (securities/main) transactions form to do both, and it would need to also inhibit use of changing shares amount. What do you think? If I don't see a response I will decide myself. Thank you.

 

[HalloweenWeed]

Pardon me if I'm asking a question that is too basic or popular, but I am having trouble Googling it as there are too many non-relevant hits:
Does the Form 'before update' event fire before a record is added, e.g. can you use that event for some VBA validation of combinations of field settings on said form, and 'cancel' the addition of a record if the combination is not valid? Or is the record added before all the fields are set?

Or, as an alternative, should I use a VBA "workspace" so I can have the addition nullified? How would I go about that?

 

[Pat Hartman]

Does the Form 'before update' event fire before a record is added, e.g. can you use that event for some VBA validation of combinations of field settings on said form, and 'cancel' the addition of a record if the combination is not valid? Or is the record added before all the fields are set?

This is a very long thread with several topics so forgive me if I repeat myself.

The form's BeforeUpdate event is the LAST event that fires PRIOR to saving a record. It makes no difference what prompted the save, whether it was Access or your code. Think of this event as the flapper at the bottom of a funnel. If the flapper is open the record gets saved or it is closed, your code displays an error message, stops the save using
Cancel = True
and then returns control to the user to fix the problem. You can use Me.Undo to clear all the data entry but that is too Draconian for words and I never recommend that as an action. The Only time I would ever do it is if the User does not have permission to update the data. But, in that case, I would not wait until the BeforeUpdate event. I would put the permissions check in the On Dirty event which fires as soon as a user types a character into any control. In this event, I check whether he is allowed to add or update and if he does not have the security level to allow this, I undo the updates and display an error message. Using this technique, I never have to lock the form which interferes with search options.

I probably also posted a link to some videos I produced as well as an Access user group presentation that shows how to use the database I built to help you learn when and why events fire.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

If you are only going to watch one of the videos, the presentation to the user group is probably the most entertaining if not the most instructive.

 

[HalloweenWeed]

Thank you for your time @Pat Hartman , that means no I can't use the before update event for my purpose.
Perhaps I should rephrase. I have the following form:

and it is a "single form" with record selectors, you can either edit or add transactions. However, there are rules about what is acceptable in each field depending on the setting in other fields. These rules are too complicated to put into control validations, and even if I did that would severely limit the order that the items could be entered (entry would become a tedious process with error messages popping up). For instance, for cash-only type transactions (deposits/withdrawals) the shares must be 0 and there should not be a "security account" entry (I guess that could be ignored with that transaction type when doing the account and statistic calculations later). For dividends, the shares must be 0. For all transaction types that are not splits the numerator and denominator must be 1.

One could put the transaction type in the validation of the other controls, but this doesn't undo the settings of the other controls when a different transaction type is selected.


I don't want to need to delete the "oops" records because of shoddy validation, and it seems Access saves a new record on the first field change. I don't want the record there at all if it was not valid. How do I go about this?

I am going to try the Workspace (.CommitTrans and .Rollback) method in vba, but when do I fire my vba validation rules? If it's on close, that won't work because the user can edit a number of records before closure, that would be rude to undo all of them. The user could also edit existing records before trying to add one, in which case I would need to do .CommitTrans before the user begins making an added record, what event do I use for that? Or am I making this too complicated, is there an easier way??? I could create different forms for edit & add record...


I have never used the Workspace (.CommitTrans and .Rollback) methods before, although I have studied what I think would be the syntax.

 

[Pat Hartman]

and it seems Access saves a new record on the first field change

ABSOLUTELY NOT. If the record is being saved after entering data into a single field, it is YOUR code that is causing the issue. You are either doing something silly like requery or actually saving.

The FORM's BeforeUpdate event should stop the record from being saved if any field fails to pass your validation logic.

I agree, with not using the control's BeforeUpdate event for validation. You can't validate relationships or presense. Those have to happen after all the data entry for the form is done so you may as well do it all in one place. I do use the control's BeforeUpdate event to validate unique values where I am not going to allow the record to be saved if the value is not unique. SSN (social security number), driver's license, etc.) fall into that category.

 

[Pat Hartman]

The problem is almost certainly the two subforms. I can't figure out what they are for. I'm getting an error message that I can't save a record because a related record is required in tblActiveDirectory. Looks like the relationship is backwards.

 

[HalloweenWeed]

ABSOLUTELY NOT...

In other forms, when adding a record, when I put in one value in any field, and close the form, it saves. I don't know how to stop that save.
In the past, I have made the form work entirely in vba recordset to mitigate this. I am looking for the ability to abort the use of the form altogether (after a dialog box selection), without saving the new record, if the fields do not pass validation, before saving the new record.

 

[HalloweenWeed]

The problem is almost certainly the two subforms. I can't figure out what they are for. I'm getting an error message that I can't save a record because a related record is required in tblActiveDirectory. Looks like the relationship is backwards.

The top form is the Broker header subform. It works off the default broker (Broker table), depending on the setting of the default Broker in the database default settings table.

The other subform is the cash account, which is also set by the Broker table, according to the default broker. I yet need to add code to save that AcctID value, it messes up the subform display if I link the subform fields, so it needs to be entered on record save (vba works for me). I am actually working on replacing that subform with simple unbound fields using embedded SQL as the control source.

EDIT: No that didn't work. The subform stays there.