Gustav Brock
gustav at cactus.dk
Fri Apr 4 07:09:58 CST 2003
Hi Joe This is an example where a compound index may come in handy. Combine this with a simple field validation rule and you're off. Consider this schematic table design: Main ID TypeFK Type ID Category1 ID MainFK TypeFK Category2 ID MainFK TypeFK Category3 ID MainFK TypeFK All ID-fields are autonumbers. Type.ID may be a normal long. Table Type contains three records, one for each category. A compound unique index is created on ID and TypeFK in table Main. Type.ID has a 1-M relation to Main.TypeFK. Main.ID and Main.TypeFK has a 1-1 relation to Categoryx.MainFK and Categoryx.TypeFK Now, add to the three fields Categoryx.TypeFK a validation rule: =x where x is the ID (1, 2 or 3) of the type of that category. In this way you can add any or no entry in a category table for an ID of the main table. If you cannot add the TypeFK field to the main table then you can move this to a link table, say tblCategory, with the fields MainFK TypeFK fitted with a compound index and a 1-1 relation to table Main. If anyone could come up with a solution without the compound index I would like to see it. /gustav > I have four tables, one is the "main" table and the other three are category > or type tables with a one-to-one relationship to the "main" table. > I want to limit a record that is in the "main" table to exist in only one of > the category tables with the flexibility of the record moving, if you will, > from one category table to another. For example, if during the creation of a > new record the user picks category1 from a combo box, he/she is presented > with the fields that are found in the category1 table. If at a later point > he/she decides to change the category to category2, the information from > category1 would be removed and the user would fill in the information for > category2. > I have coded this before and everything worked out well, but what I was > wondering is if the ability to limiting to one category table could be > achieved using some form a relationship rules in Access. Or can this only be > accomplished with coding?