Enforce Referential Integrity from a Query to a Table

[leicklda]

Please help...

Big picture: I have an inventory - I want a 3-part prefiexed identifier string to illustrate what category heierarchy each product is. For example a fairy tale book would be "BKS-CHD-FRY" and a child's board game would be "GAM-BRD-CHD" I want these codes automatically generated for each product by setting relationships up between tables T1 (Books=BKS), T2 (Child=CHD), & T3 (Fairy Tales=FRY). I have this working well but the problem is that once I set the abbreviation code for T2, I can't revise the code later b/c it won't update in T3 because I can't force the query result to update the data in table 3.

I have a 3 tables and 2 queries

Tb|T1
----------------------
[T1] (Type 1 ex: Books)
[AB1] (Abbreviation 1 ex: BKS)


Tb|T2
----------------------
[AB1] (Abbreviation 1 - ref. int. enforced from Tb|T1)
[T2] (Type 2 ex. Childrens)
[AB2] (Abbreviation 2 ex. CHD)


Qry|Q2
-----------------------
SELECT [T2].[T2], [1AB] & "-" & [2AB] AS C2, [T2].[1AB]
FROM T2;
********So this creates the first two code prefixes. Ex: BKS-CHD


Tb|T3
[C2] ****Here is the problem: [C2] is based on a Query so it own't update if I change the value of [2AB] after I assign it as a category for T3. The pulldown list will reflect the code change, but it won't actually update any code already place in the table
[T3]
[AB3]


Qry|Q3
SELECT T3.C2, [C2] & "-" & [3AB] AS C3, T3.T3
FROM T3;
*******So this completes the 3rd part of the code BKS-CHD-FRY


Any help would be greatly appreciated.

Thanks

 

[KenReay]

Hi

THe first question might be, why having devised a coding system, and assigned code do you want to change it?

But if we put that to one side, the way around your problem is to amend your design.

There are two ways around your problem:

Set the Cascade Update property to true, in the relationships screen for the affected relationships

Or

THe Code tables for Types of Book should consist of:

CodeId Autonumber PrimeKey
Code String(3) - Indexed Unique - eg the three character Code
Description String (50)

You store the CodeId in the other table(s) as the foreign key to the table(s) of codes, thus in effect the three digit code, is simply a short description, which may be changed at will. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leicklda]

Thanks for the reply. Your first idea is what I want to do: turn Cascade Update on for the relationship. The problem, though, is that the option to do so is not available when linking a query to a table.

When linking a Table to a Table, I can easily do this. But when linking a Query to a Table, I cannot - the options are greyed out in the Edit Relationships box.

Any ideas?

Thanks