Solved Help setting up a new Investment Portfolio database using normalization

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

OK, it works (before entry) now. IDK if it works on entry yet, not tested, not the point. But I needed to remove the linking between the master and child forms, then put in this bit of code:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

    If txtCashAcctID < 1 Then txtCashAcctID = Me.Form.subAcctDfltCash![AcctTblKey]

End Sub

This sets the Cash Account in the transaction. The field is only ever zero on a new record.

Latest build attached.

 

[Pat Hartman]

when I put in one value in any field, and close the form, it saves

Put validation code in the form's BeforeUpdate event to ensure that the record will not save if it is incomplete. I don't know how else to say it. This is the instruction you have been given several times in this lengthily thread. Only YOU know what constitutes a valid/complete record. Access can't know this. If you dirty a field and erase what you typed, Access will save the record. YOU are the one who has to decide that this is invalid and the Form's BeforeUpdate event is the place to stop the save.

 

[HalloweenWeed]

Put validation code in the form's BeforeUpdate event to ensure that the record will not save if it is incomplete. I don't know how else to say it. This is the instruction you have been given several times in this lengthily thread. Only YOU know what constitutes a valid/complete record. Access can't know this. If you dirty a field and erase what you typed, Access will save the record. YOU are the one who has to decide that this is invalid and the Form's BeforeUpdate event is the place to stop the save.

OK, thank you. I'm currently working the bugs out of the transaction form.

 

[HalloweenWeed]

I had to remove "enforce referential integrity" on 3 of my relationships to get the transaction entry form working (frmXactnEntryMstr):

Is this bad? I read somewhere something that made me think it was strongly inferring that all relationships should have "enforce referential integrity."

However, I still need to code the validation rules. In doing so, I see that if the form throws an error on save, closing the form without saving is possible (of course), with a couple of error dialog boxes thrown up. I've entered 10 transactions successfully (for all that's worth). I see I would want the ability to enter a new account while the transaction form is open, as I had one time the form created a new record before I was able to select the account.

Edited 8/10: added name of transaction entry form.

 

[Pat Hartman]

I had to remove "enforce referential integrity" on 3 of my relationships to get the transaction entry form working (frmXactnEntryMstr):

Then you need to rethink what you are doing. RI is a very important aspect of proper database design.

Did you see my comment that the relationship between AcctDirectory and Xactin is BACKWARDS? That would certainly cause the form to not work correctly.

 

[HalloweenWeed]

Then you need to rethink what you are doing. RI is a very important aspect of proper database design.

Did you see my comment that the relationship between AcctDirectory and Xactin is BACKWARDS? That would certainly cause the form to not work correctly.

Yes, I read all comments carefully, and give them thought.
I tried reversing the relationship, tried only incl. rows where both fields equal, and still it refused to make a new record. So I had to remove enforce referential integrity, it was the only option that worked for me. Sorry, but I have to do what works, right or wrong. If you can tell me, or show me, something that works here I would be happy to fix it.

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal." When I google it, I get too many explanations I don't understand or are the same. If somebody would just tell me: are the arrows supposed to point to the table keys, or away from them? That I can understand. I have chosen the best fit to the wording above to my best understanding. When reversing it doesn't help (and I reversed all of them, because if my understanding is backward, then all of them are backward), it doesn't matter that you stated they're backward, because it doesn't work either way. Sorry, but I will not do what doesn't work even if somebody tells me to.

Here's what I have now:

Latest build:

 

[MajP]

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal.

These are excellent articles to help you. Please read. Explained in detail with examples. @isladogs

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

isladogs.co.uk

Note that in the Relationship builder the Join type (inner, outer, left, right) has nothing to do with referential integrity. The only purpose is for creating a "default" join to assist when making queries. It is only a default and can be changed in any query. Most people simply make this an inner join (only return records where both are equal). If you need something else in a query then fix it there.

 

[mike60smart]

Yes, I read all comments carefully, and give them thought.
I tried reversing the relationship, tried only incl. rows where both fields equal, and still it refused to make a new record. So I had to remove enforce referential integrity, it was the only option that worked for me. Sorry, but I have to do what works, right or wrong. If you can tell me, or show me, something that works here I would be happy to fix it.

I have never completely understood the language, in the way that it applies to my tables: "include all records from tbl1 and only those records from tbl2 where the joined fields are equal." When I google it, I get too many explanations I don't understand or are the same. If somebody would just tell me: are the arrows supposed to point to the table keys, or away from them? That I can understand. I have chosen the best fit to the wording above to my best understanding. When reversing it doesn't help (and I reversed all of them, because if my understanding is backward, then all of them are backward), it doesn't matter that you stated they're backward, because it doesn't work either way. Sorry, but I will not do what doesn't work even if somebody tells me to.

Here's what I have now:
View attachment 109383

I have done some work on my transaction form, and am still beta testing the workspace portion of the workings, so it may not be ready for testing relationship changes:

I always apply Option 1 in the Relationship Window.
In Queries you can apply whichever Option depending on requirements.

 

[Pat Hartman]

1. Update the data in ALL the tables to remove 0 as a value for the foreign keys (columns that point to the PK of a different table)
2. Update the design of all the tables to change the default for ALL foreign keys to null.
3. When the FK is required, set required to true. When it is optional, set required to no.
4. Remove all the join types. They are just confusing the diagram.
5. Create all the relationships that are missing. If you get an error message, you still have bad data in the table. That must be fixed before you can create the relationship.

MS has elected to make the default for all numeric datatypes 0. While this helps novices who do not understand how to handle null values, it gets in the way of creating foreign keys since 0 is NEVER a valid value for a FK since the autonumber doesn't generate a 0 value automatically.

Once you get better at this, you will understand why 0 is not necessarily a good default value. 0 means something. So for example, if you are a teacher and you run an append query to append a new test to each student and you then go and fill in the values one by one, If you miss one or a student has not yet taken the test and so has no score, you are left with a student having a 0 as his score for that particular test. Think about what that does to his average.

The average of 80, 0, 100 is 60 but the average of 80, null, 100 is 90. Big difference and pretty scary to a parent who happens to be looking at his child's records. Access ignores nulls when domain functions are applied but does not ignore 0 because 0 means "something" and null means "unknown". It doesn't make any sense to average an unknown value but it does make sense to include 0 when calculating an average So, the point is, at some time, you will know enough to be able to make a rational decision as to which numeric columns should default to 0 and which should default to null. Another example is a quantity field. 0 makes no sense as a default for quantity. So, therefore, we make the default null and set the field to required which forces the user to always enter a quantity. In some instances 1 could be a valid default for quantity but never 0.

Here is the error you get when the table contains invalid values in a foreign key field.

Here's a sample db with some useful functions for setting property values. ALWAYS make a backup before running any code like this.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[HalloweenWeed]

These are excellent articles to help you. Please read. Explained in detail with examples. @isladogs

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

isladogs.co.uk

Note that in the Relationship builder the Join type (inner, outer, left, right) has nothing to do with referential integrity. The only purpose is for creating a "default" join to assist when making queries. It is only a default and can be changed in any query. Most people simply make this an inner join (only return records where both are equal). If you need something else in a query then fix it there.

OK, it seems my understanding of relationships was not flawed, and I didn't know about the corresponding SQL type associated, but I did perceive the similarity previously.

I always apply Option 1 in the Relationship Window.
In Queries you can apply whichever Option depending on requirements.

Thank you @mike60smart , that's very helpful.

THIS:

1. Update the data in ALL the tables to remove 0 as a value for the foreign keys (columns that point to the PK of a different table)
2. Update the design of all the tables to change the default for ALL foreign keys to null.
3. When the FK is required, set required to true. When it is optional, set required to no.
4. Remove all the join types. They are just confusing the diagram.
5. Create all the relationships that are missing. If you get an error message, you still have bad data in the table. That must be fixed before you can create the relationship.

MS has elected to make the default for all numeric datatypes 0. While this helps novices who do not understand how to handle null values, it gets in the way of creating foreign keys since 0 is NEVER a valid value for a FK since the autonumber doesn't generate a 0 value automatically.

This is what I needed to know! TY very much @Pat Hartman !
So, I take it by your instruction "Remove all the join types" you mean what @mike60smart said? Option 1? OK, sounds like a good plan. This is what I have been missing from the beginning!

 

[Pat Hartman]

I always apply Option 1 in the Relationship Window.

Option 1 can be different depending on which table you add first. If you add the tables from the one side first, then option 1 works but not in all cases.

EDIT: Sorry, option 1 doesn't change, it is options 2 and 3 that switch. Option 1 is always the equi-join

 

[HalloweenWeed]

So, just to confirm I understand this correctly: I can leave indexing ON for these FK's? That won't cause a fail to make (add) a record if the value is Null?
EDIT for clarity: I mean "indexed (duplicates OK)" and field not "required."

 

[HalloweenWeed]

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

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.

Just another reason I hate them: I was getting the wrong AcctID value from my AcctID combobox in frmXactnEntryMstr. It was throwing a 'entry needed in tblAcctDirectory' error. I found that I had bound the wrong column to the AcctID. This complicated matters for me when troubleshooting the forementioned problems with the table relationships, it could've been that it might have took me hours longer to debug. So I did the proper thing: I edited the "bound column" in the combobox properties (the column I needed was there). But sometimes it returned a "2" (there is no account #2) and other times (different accounts) it returned the proper account number (13, 15, or 16). It took me 2 hours of troubleshooting to figure out that nothing else was wrong, I just needed to delete the control and remake the combobox from scratch. Boom, problem solved, works fine now. #*&&(&@#((&@(&*#$ comboboxes. Grrr. FYI.

Relationship now:

Thread fin.

 

[Pat Hartman]

So, just to confirm I understand this correctly: I can leave indexing ON for these FK's? That won't cause a fail to make (add) a record if the value is Null?
EDIT for clarity: I mean "indexed (duplicates OK)" and field not "required."

NEVER add your own index for a FK. This is not clear in any documentation but Access makes its own index when you define relationships using the relationship window. If you also add an index, you end up with two. I haven't experimented with this. Access may be smart enough to not add a second index if you create the index in the table first and then add the table and relationship to the relationship window. But, if you add the index to the table after you have created the relationship, you end up with two indexes and that can cause corruption.

When I said remove the join types, I was referring to the Relationship window. You need the join type when you create a query. The query normally assumes an inner join. You have to change it to left or right.

To confirm what I said about "join type 1" when making queries, Just make two new queries. Add tblA first then add tblB to the right. In the second query add tblB first and than tblA to the right. Read the three questions to see the difference.

PS - Sorry Option 1 doesn't change depending on the position of the tables but options 2 and 3 swap.

 

[HalloweenWeed]

NEVER add your own index for a FK. This is not clear in any documentation but Access makes its own index when you define relationships using the relationship window. If you also add an index, you end up with two. I haven't experimented with this. Access may be smart enough to not add a second index if you create the index in the table first and then add the table and relationship to the relationship window. But, if you add the index to the table after you have created the relationship, you end up with two indexes and that can cause corruption.

So now that I already have, should I turn off indexing, in (table) relationships, for the FK's?
I made the FK's indexed first, before adding the relationship(s). Except maybe CashAcctID, I'm just not sure about that one.

 

[Pat Hartman]

Finding these duplicate indexes is a little complex. I never noticed them until I did my first upsize to SQL Server of a database that had some because the Indexes dialog doesn't show them - they are hidden. I think this is a mistake. While it is true that the whole thing is confusing to novices, it is more confusing to hide things like this from them especially when they can interfere with performance. It took a bit for me to figure out what causes them. Here is a picture from an old database that shows an example. I have a documentation tool that I built and which I cannot publish here since I am not inclined to give it away for free (more later). The background is a find duplicates query that looks for any instance where the table name and field name occur more than once. This example is illuminating because it shows two situations.

We'll start with the second set first for clarity. The second duplicate is for FieldID. The fourth line of the indexes query shows the manually created index. It is named FieldID. This is the one you create by setting the indexed property on a column of the table in design view. Access automatically assigns the FieldName as the name for the index. The third line shows an index for FieldID with a long unique id frequently called a GUID (globally unique ID). Looking at the Indexes dialog window for the tblBookmarks, you do not see any index with this GUID name because the index is hidden.

The first set is for DocID. This case is a little different. In the second row of the query, It shows the GUID defined by Access for the hidden FK index when I created the relationship in the relationship window. The second index that includes DocID (named UniqueIDX by me and created using the indexes dialog because this is the only way you can create a multi-column index that is not the PK) appears on the first line of the indexes dialog. But, you can see by looking at the Indexes dialog window, this index is actually not a duplicate since it is a compound (made of multiple columns) index. It says that the combination of DocID (which uniquely defines a document) plus BookMarkName must be unique. This is to support the rule imposed by Word that bookmarks within a document have unique names.

Although I am not willing to post my documentation tool, If you strip the BE to nothing but tables, I don't need data, I can give you a list of all your duplicate indexes. I know of no way short of Looping through the TableDefs collection and then through the indexes for each TableDef to find this information. My code creates three tables from the TableDefs collection
Tables
ColumnNames
Indexes
ColumnNames and Indexes are both children of Tables but are not related to each other.

As I said earlier, I don't know if Access is smart enough to not create duplicate indexes and I don't have time at the moment to delve into this. I know the problem exists and I can find yours specifically if you need me to.

If you haven't already done it, goto the backstage view and turn off the Access "feature" that automatically creates indexes based on the suffix of any column name such as ID, CD, and a few others which you can add to. This is fine for novices but like many crutches, it is dangerous and so should be turned off once you are sufficiently knowledgeable to understand the need for indexes to begin with.