Solved Help setting up a new Investment Portfolio database using normalization

[mike60smart]

Thank you. Will do.

One thing though, I hate Access comboboxes. I tend to get elaborate with the vba to avoid their use.

Hi
As Pat said, Combobox's are the easiest and in my view the best way to select a specific item from a List.

 

[HalloweenWeed]

Do you hate them because you don't understand how they work? They are the absolutely simplest way to select an item from a list. Do you not understand how to use a table to manage the list of items? There are things to hate about Access but I would never have thought of including combos in that list.

OK, you asked for it:
I hate comboboxes for several reasons. Pardon me if I seem vague in some reasons as it's been years since I had the difficulties with them. I dislike the way the fields are handled as "columns," and the confusing string listing of them - it's (sometimes?) difficult to match the columns with the query text ("Row source"), and even more confusing with some of the columns are 0" wide to hide them as you tend to think of the column number as shown in what you see, rather than the SQL order. Then you "bind" the control to one "column" (numerical designation) rather than binding to the field name. I've had headaches due to the wrong column bound, which is not necessarily the column shown in the dropdown. Then, if you want to display more than one field, the confusion gets even worse.

And the combobox control doesn't give you any auto-width functionality, for either the columns nor the control, neither on design nor on use (no "grow" feature), so if any text exceeds the column width, it is cut-off, and there is no way to test this without actually running the control and looking at the (test sample) data. It's all so opaque, feels like designing the form blindfolded. At least with a regular form textbox you have the option to let it grow. Why can't you link the SQL to a Query instead of SQL within the control? That would allow a little more flexibility with your dropdown list parameters.


Then, the other gripe: no "search as you type" functionality such as you see on many website forms. Especially useful for longer selection lists. It is inexcusable how terrible long selection lists are handled in Access.

The attached database shows a prime example of what I prefer, it not only reacts to text as you type, but it shows on a pick list everything in the list before you type as well. No scrolling necessary.

This is what I prefer to do rather than use a combobox (see attached):

 

[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

 

[Pat Hartman]

OK, you don't understand how they work so you are constantly fighting with them. Perhaps learning more about them will help. The RowSource query is what controls what you see in the list. The columns are always in the order that matches the query. So, there are two bound elements. The RowSource which is the list you see. It is bound to a table, query, or value list or even built on the fly. The other bound object is the ControlSource. This is the UNIQUE ID, normally the PK of the table selected by the RowSource query. So for example, if the RowSource = Select CustID, CustName From YourTable Order By CustName. You use 0 as the width for the first column to hide it and the combo displays the customer name. As you type, the combo is advanced to zero in on what you type.

0" is used to hide columns. You can also rearrange the Select clause in the RowSource query to avoid selecting columns you don't want to see (you always need the PK column though because that defines uniqueness AND is what is stored when you select an item. The text field is NOT stored, the PK is what becomes the bound column). By convention, we select the PK first and almost always set its width to 0 to hide it. That allows the first non-zero width column to be the column that you see. You should ALWAYS sort the query on this column. If you don't the combo will not work correctly for searching. Search as you type works just fine provided the query has been sorted on the field you use to select the value. You can arrange the columns however you want but you do yourself no favors by deviating from the standard order of PK first, display column second.

Sometimes, we want to have more data visible than just the single text field once we select a record. For example, if your list is employees, Your RowSource could look like -- Select empID, LastName & ", " & FirstName As FullName From YourTable Order By LastName & ", " & FirstName. This lets you see last name and first name. If you prefer first name then last name, change the concatenation and Order By

True, you need to set up the widths initially but how many columns are you working with? How hard can this be? You only have to do it once anyway. If you like the way listboxes work better, use them instead.

And we finish with - Access is a RAD (Rapid Application Development) tool. It does things the way it does them so you don't have to write code. Of course, if you don't like the way Access does things, you will always be unhappy with it and fight with it continually as well as feeling the need to write tons of code that isn't technically necessary so you can make Access work the way you want it to work. You might consider using a different tool that you find superior if you don't take advantage of Access' RAD features anyway.

PS, If account types are mutually exclusive, there should be one column rather than 9.

 

[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

 

[Pat Hartman]

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

Now, that is a really nifty solution and an excellent use of a class.

 

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