I am redesigning a database that I did about two years ago.

It is a database designed to handle nonconformance issues of all shapes and sizes for my company.

An NCR (Nonconformance Report) can have one reported error or many. I have devised a way to track these errors for reporting purposes. Each NCR has a desciption field to describe the error (1-Many so that an NCR can have more than one). This description is generalized in the following way:

tblErrors
pkErrorID - Autonumber
ErrorName - Text
fkErrorSubCategoryID - Number

tblErrorSubCategories
pkErrorSubCategoryID - Autonumber
ErrorSubCategory - text
fkErrorCategoryID - Number
Now I currently have these all as one to many relationships in my relationship screen.

tblErrorCategories
pkErrorCategoriesID - Autonumber
ErrorCategory - Text

These are all related in the relationship screen.

I have a fourth table which contains the following:

tblNCRIssues
pkNCRIssueID - Autonumber
fkErrorID - number
fkNCRHeaderID - number
Description - memo

Would it be wrong to remove the relationships between the Error tables but leave the fks with their references, then add an fk for each in "tblNCRIssues"?

Also, would the above mentioned approach require me to then have an fk from "tblErrorCategories" in "tblErrors"?

The reason I want to know is that I want to be able to use a continuous form to assign an NCRIssue with its appropriate error, and that is usually built from the category level down through more detail to the actual error. The way I have it set now, the combo boxes don't change with records, even if on single form, and when on continuous forms they all show the same (which I know is just how continuous forms work).

I have attached the db with just the relevant tables.

Oh, my, what an interesting question. (Think of the old Chinese curse, "May you live in interesting times.")

There are two schools of thought here. One school says that if you have a relationship between two entity classes, that relationship should be echoed by a data relationship between the two tables representing those classes. However, when there is a second layer that imposes another relationship, the question would be how that relationship works with the first one.

The purist says you must build a layered query so that you can apply the relationships in turn. That way, while it is complex, it accurately preserves the transitive relationships in the A ==> B ==> C relationship.

The pragmatist says if the relationship is subject to a distributive rule that it would be OK to move the FKs to the next layer down as a matter of optimizing the lookups for reporting or faster queries. In which case there might be a justification for turning

A ==> B ==> C into A ==> B and (A,B) ==> C

As long as the keys in question are static once assigned, this does not really matter. If they are dynamic in any way, though, you have just opened a can of worms.

if a subcategory is ALWAYS a subcategory of the same category, then you only need to store the subcategory. The category can be obtained from the subcategory

eg, take a country, and a continent

Germany is always in Europe, so you dont need to store both Germany AND Europe in the customer record. just Germany.

if you store both, there is always a danger of getting stuff wrong, and storing, say Germany as the country, but Africa as the continent.

Oh, my, what an interesting question. (Think of the old Chinese curse, "May you live in interesting times.")

There are two schools of thought here. One school says that if you have a relationship between two entity classes, that relationship should be echoed by a data relationship between the two tables representing those classes. However, when there is a second layer that imposes another relationship, the question would be how that relationship works with the first one.

The purist says you must build a layered query so that you can apply the relationships in turn. That way, while it is complex, it accurately preserves the transitive relationships in the A ==> B ==> C relationship.

The pragmatist says if the relationship is subject to a distributive rule that it would be OK to move the FKs to the next layer down as a matter of optimizing the lookups for reporting or faster queries. In which case there might be a justification for turning

A ==> B ==> C into A ==> B and (A,B) ==> C

As long as the keys in question are static once assigned, this does not really matter. If they are dynamic in any way, though, you have just opened a can of worms.

What do you mean by static versus dynamic? By dynamic do you mean that a record in C might belong to one record in B at one time and a different record in B at a different time?

if a subcategory is ALWAYS a subcategory of the same category, then you only need to store the subcategory. The category can be obtained from the subcategory

eg, take a country, and a continent

Germany is always in Europe, so you dont need to store both Germany AND Europe in the customer record. just Germany.

if you store both, there is always a danger of getting stuff wrong, and storing, say Germany as the country, but Africa as the continent.

What then is a way to handle forms that use this information, I am having a hard time knowing what to do with my current setup. I built a form that allows me to select the error I want by using the cat and subcategory. But then when I go to a new record the combo boxes show the choices I made in the previous reocord. And, if I go back to the previous record, the combo boxes do not display the information for that record, they act independently, as pickers only. Would this be a case to have a text box behind them that displays the info? And when I change records have the combo boxes reset to blanks?