Append Reverse Cross Reference?

[misscrf]

I have a table that acts as a cross reference to related records. No we can't get different data, no we have no control over what we have, we just need to be able to manage what we have been given. lol

Anyway, what I have created so far is working well. I have a subform on a tab of a main customer entry form. It says "related customers".


My issue is this: When you go to one record, you may see 4 customer records relating to it. If you go to any of those 4, you only see the first record, as if it is a master.


I am wondering if there is a way to right a query, that can update the table to populate all relationships.


Here is what it looks like now:


CustomerID RCustomerID
33219 242110
33219 242111
33219 242112
33219 242113
242110 24219
242111 24219
242112 24219
242113 24219


So when I am on record 33219, I see that 242110, 242111, 242112, and 242113 are related. But, if I go to any of the records for 242110, 242111, 242112, or 242113, I only see that 33219 is related. I would like to see that 242110, 242111, 242112, and 242113 are related too.


I would like the table to look like this:


CustomerID RCustomerID
33219 242110
33219 242111
33219 242112
33219 242113
242110 24219
242110 242111
242110 242112
242110 242113
242111 24219
242111 242110
242111 242112
242111 242113
242112 24219
242112 242110
242112 242111
242112 242113
242113 24219
242113 242110
242113 242111
242113 242112


I can see it in my head (along with a monkey and a clown on a unicycle...) but I can't figure out how I would write this in what I would think would be an append query. Any thoughts?


Thanks!




misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Chad1984]

Hmm. I'm not expert, so I'm not going to pretend I have the holy grail in hand here- just some ideas from someone else that's learning.

I don't really know how you intend to use this data- you can't just say something like

SELECT rcustomer_id FROM customer WHERE customer_id = (SELECT customer_id FROM customer WHERE rcustomer_id = [CurrentCustomer]);

...or something along those lines? So that you look up the customer in the table to find the master record and then use the master record to find all the other records?

I think I've figured out a way to get the table you want, if it must indeed be created. It shouldn't even be necessary- and hopefully there is an easier way.

You would use VBA code to generate all the extra records. I'll go through what I think would work...

1. Open a recordset of your current table
2. Loop through the records in the recordset
2a. Hold the "current" related record in a variable
2b. Open up a query that selects all the records with the current record's master, except for the record with the current related customer.
2c. Loop through this second recordset
2c1. Insert a record into a temporary table with a customer_id of the current related customer and an rcustomer_id of the current field in the second recordset.
3. Select the contents of the temporary table into the table you want.

I hope that made some little sense to you. It would probably need some tweaking- hopefully it at least is in the right direction!