Complex relationship

Due to choices beyond my control I am faced with a tough bit of SQL to compose.

I have four tables. Let us callt hem A, B, C & D.

Table B has had it's content wholesale copied into table A. Table A is dynamic and changes table B will never change.

Table B has a non explicite (unenforced) but real one to many relationship to C.

[B] --< [C]

This is due to a unique number for each record within each row of B and none, one or many instances of that number within C.

These number DID NOT copy to A from B as A will be useing an autonumber instead.

The relationship between A and B is the presence of something the developers chose to call the SB_Key it is is 99.9% unique with odds of arround 1 in 1000 chance of two identical values in two different fields. However to get SB_Key duplication the records have to be made in the same second in the same table on the same day (etc) so for our purposes they are all different as no user I know can type taht fast.

[A] -- [B] A has a one to one relationship to B

Now we come to table D. D has had the content of C copied into it. C is static and D is dynamic.

Again the SB_Key is the one to one link between the tables.

[C] -- [D] C has a one to one relation ship with D.

So far all of these relationships are known only to the programmers and are not explicit.

Further tables B and C are in another database file altogeather!

[A] -- [B] --< [C] -- [D]

No comes the bit with which I am haveing some trouble.

[A] --< [D] A should have a one to many relationship with D. The relationship of A to D must match the relationship from B to C.

The data is in place and the plan is to "run an update Query" to replace the Foregn Key in table D with the correct autonumber-generated value from table A based on the relationship between B and C.

I can not whoever seem to comeup with the SQL that will do this.

If I have to I can create the SQL dynamicly in VBa code and create VBa functions to go get information.

However there is a lot of data and the more functions the slower the system. It will be run on PCs ranging from Pentium II to 3 Gig Athlon XP and it is vital that the computer not crash or appear to crash and cause the user to press reset (thus corrupting thier data).

Help.Sounds like you want to do this:

update d
set a_id =
( select a_id
from a, b, c
where a.sb_key = b.sb_key
and b.b_id = c.b_id
and c.sb_key = d.sb_key
);