Particular relationship

[iib]

I will simplify my question to the essential issues:



In principle I have a very simple case. Three tables: Category, Subcategory ans Items (each item belongs to a unique Category+Subcategory). The relationship between these table seems to be obvious.

BUT... I have a particular condition that confuses me. The Subcategories are not unique. That means certain Subcategory can belong to more than one Category.

How should I build the relationship in this case? I am struggling with this for a few days and still could not find the answer. I read tens of examples but couldn't find anything similar with my case.

Thank you in advance for any advice you can give.

[Rod]

The Subcategories are not unique.

By that I assume you mean the codes (or values) are not unique. The tough question to answer is whether the subcategory code/value has any meaning on its own or is it only in combination with the category code/value that the meaning is realised. There is a subtle difference between the two. Maybe a couple of ERD examples will help to clarify what I'm saying.



The upper ERD applies when the subcategory has no independent meaning. Yes, subcategory code/values will be duplicated in the table but this is not the duplication referred to in normalisation techniques. The lower ERD applies when it does have a meaning. (There may be a relationship between Category and Subcategory in the lower ERD.)

[iib]

Definitely we are in the second situation (lower ERD).

The Subcategory itself has no meaning. Only the combination Category+Subcategory makes sense.

One good example to explain why the Subcategory alone is meaningless:

- Categories: Triangles, Squares, Circles
- Subcategories: Red, Green, Blue

If we specify for one item only the Subcategory we know only it's color, but the shape remains unknown.

[Rod]

Probably looks something like the following:



'Combination' contains all the valid Category-Subcategory combinations.

[iib]

Thank you for the answer. However I still have a problem:

Within the table Item I get only the IDs of Category and Subcategory, not their real names. How can I display the real names? Because seeing only that IDs you can not make a choice.

For each FK I used the Lookup Wizard. Probably here is my error.

[Rod]

Judicious use of lookup fields are OK if you understand the dangers - no, too strong a word, consequences - of their use. The wizard will only 'look' one table away whereas you need to look two tables away, through the table I named 'Combination' to the sources on the tables 'Category' and 'Subcategory.'

You can write your own SQL and use this in place of the wizard's SQL thus preserving the lookup field concept. However the better plan is to accept that the foreign key on the 'Item' table is just that, a key, and then implement the lookup only in the GUI objects, that is forms and reports. A function written in a standard module could accept a key and return a text comprising a concatenation of the category and subcategory names.

If you want help with either of these solutions then post back.

[iib]

Unfortunately I will not be able to write some SQL code. But seems to me that this is the fastest way. To deal only with tables for the moment. So I would appreciate if you can provide me this code example, just to put in place the tables. Afterwards I'll gladly take the challenge to deal myself with the other option: forms and reports. It will be an excellent exercise for me.

Even I'm afraid I will ask for your kind assistance from time to time

[Rod]

I set up a small prototype for the example. Here is the Relationship Diagram.



The purpose of giving you this is to explain my names; you should use your own names in your code.

Here is one way of implementing a 'lookup field' that involves concatenating the category and subcategory values/codes. Note there are two columns; the first is the bound column but hidden while the second column is visible.



The row source code is as follows.

SELECT tblFullCategory.FullCategoryID, DLookUp("Category","tblCategory","CategoryID = " & [tblFullCategory].[CategoryID]) & " - " & DLookUp("Subcategory","tblSubcategory","Subcategor yID = " & [tblFullCategory].[SubcategoryID]) AS Expr1 FROM (tblSubcategory INNER JOIN (tblCategory INNER JOIN tblFullCategory ON tblCategory.CategoryID=tblFullCategory.CategoryID) ON tblSubcategory.SubcategoryID=tblFullCategory.Subca tegoryID) LEFT JOIN tblItem ON tblFullCategory.FullCategoryID=tblItem.FullCategor yID;

Here is a three column solution.



For this the row source is:

SELECT tblFullCategory.FullCategoryID, tblCategory.Category, tblSubcategory.Subcategory FROM (tblSubcategory INNER JOIN (tblCategory INNER JOIN tblFullCategory ON tblCategory.CategoryID=tblFullCategory.CategoryID) ON tblSubcategory.SubcategoryID=tblFullCategory.Subca tegoryID) LEFT JOIN tblItem ON tblFullCategory.FullCategoryID=tblItem.FullCategor yID;

If you don't implement either solution as a lookup field then you may use either SQl as the row source for a combo/list box in a form design.

If you want VBA procedures for returning the (sub)category codes/vbalues then post again.

[iib]

Thanks very much, Rod, for guiding me through this issue.

I applied your SQL code and - of course - it works as I wanted. I learned a good lesson out of here: that tables are really not meant for the user. They belong to developer's territory. The user should only deal with forms. They are supposed to display better (much friendly) the information.

And this will be my exercise now I will try to create the necessary forms for the user to manipulate the data. However, I will ask for your help if I will fail somewhere.

Thanks again for your kind assistance.

[iib]

I started to play with SQL code and I have a question. Regarding the well known topic of cascading combo boxes.

This method definitely can be applied to forms. But can we apply it also to tables? That means:

- In a field we have a combo box to select some values: Categories, from our exercise.
- In a second field we have another combo for Subcategories.
- We have already predefined the assignment of Subcategories to Categories (in table FullCategory).
- We created the proper relationship between our tables.

- Can we use an SQL code for the Subcategory combo, so it will display only the values corresponding to the Category already selected? And, I repeat, I ask if this can be done directly in a table (Item table), not in a form.

[Rod]

I can find no way of implementing this as a lookup field (i.e. at the table level as you asked).

Why do you insist on a lookup field? Whatever GUI you use to manage your data will be some type of form. Continuous forms are not so easy in this respect; you have to requery the combo box each time you move up or down the list.

[iib]

I don't have a special attraction for lookup fields Now that you open my appetite for SQL I simply want to learn. And I explore directions unknown to me, but of course well known for experimented users.