How could I distribute unique values from Table B to Table A based on values matching in two columns common to both tables?
Here is the Table configuration:
Table A has the following Columns (and Values):
PrimaryKey (101, 102, 103, 104)
ForeignKey (NULL, NULL, NULL, NULL)
Fruit (Cherry, Apple, Banana, Okra)
Color (Red, Green, Yellow, Magenta)
Table B has the following Columns (and Values):
PrimaryKey (901, 902, 903, 904)
Fruit (Cherry, Apple, Banana, Tomato)
Color (Red, Green, Yellow, Cyan)
Where the values in the Fruit and Color columns of Table A match the values of the same columns of a record in Table B I want to copy the PrimaryKey of Table B to the ForeignKey of Table A, linking those two records, then continue to check for matches, linking records where it finds a match.
So, when it's done Table A will look like:
PrimaryKey (101, 102, 103, 104)
ForeignKey (901, 902, 903, NULL)
Fruit (Cherry, Apple, Banana, Okra)
Color (Red, Green, Yellow, Magenta)
Here's another slightly different example to illustrate what's a match and what's not:
Table A (and Values):
PrimaryKey (101, 102, 103, 104)
ForeignKey (NULL, NULL, NULL, NULL)
Fruit (Cherry, Apple, Banana, Okra)
Color (Red, Red, Red, Red)
Table B (and Values):
PrimaryKey (901, 902, 903, 904)
Fruit (Cherry, Apple, Banana, Tomato)
Color (Red, Green, Yellow, Red)
In this example Table A should end up like this:
PrimaryKey (101, 102, 103, 104)
ForeignKey (901, NULL, NULL, NULL)
Fruit (Cherry, Apple, Banana, Okra)
Color (Red, Red, Red, Red)
The only ForeignKey copied was where both Cherry and Red matched between the two tables. Other than that any record from Tablel B can be linked to (match) and record in Table A, exactly once. Once a fruit from Table B is "used up" it can't be used again. The ForeignKey values in Table A, copied from Table B, must be unique.
I know it's not simple, so please, no comments consisting only of "This is hard to do."
If it would be easier to do in VB than using a SQL monster query, that would work for me too. (Don't just tell me "This would be easier in VB." please provide code, I am NOT an experienced programmer, I have to look at an example to craft a Select|Case statement. ;-)
Thank you for having a look.
