Update reverse cross reference?

[nospam]

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!

[Allen Browne]

Could you use a UNION query to get this both ways around:

SELECT CustomerID AS A, RCustomerID AS B FROM Table1
UNION ALL
SELECT RCustomerID AS A, CustomerID AS B FROM Table1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"no****@thankyou.com" <mi*****@yahoo.comwrote in message
news:11**********************@l22g2000prc.googlegr oups.com...

>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!

[nospam]

On Aug 20, 10:41 am, "Allen Browne" <[e-mail]>
wrote:

Could you use a UNION query to get this both ways around:

SELECT CustomerID AS A, RCustomerID AS B FROM Table1
UNION ALL
SELECT RCustomerID AS A, CustomerID AS B FROM Table1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thank you for the quick response. I tried this out, and it just gives
me this part of things:

242110 24219
242111 24219
242112 24219
242113 24219

[nospam]

Hey, I got one that kind of works... (you got me thinking!)

SELECT tblRelated.RelatedMemberID, tblRelated_1.MemberID
FROM tblRelated INNER JOIN tblRelated AS tblRelated_1 ON
tblRelated.MemberID = tblRelated_1.RelatedMemberID;
The only thing lacking is that it gives me this:

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

In case that is too much to look at, it is showing each customer
related to itself, as well as everything else. I would like to
supress that somehow. Any thoughts on that?

Thanks!

[Allen Browne]

How about adding criteria under MemberID of:

<[RelatedMemberID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"no****@thankyou.com" <mi*****@yahoo.comwrote in message
news:11**********************@m37g2000prh.googlegr oups.com...

Hey, I got one that kind of works... (you got me thinking!)

SELECT tblRelated.RelatedMemberID, tblRelated_1.MemberID
FROM tblRelated INNER JOIN tblRelated AS tblRelated_1 ON
tblRelated.MemberID = tblRelated_1.RelatedMemberID;
The only thing lacking is that it gives me this:

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

In case that is too much to look at, it is showing each customer
related to itself, as well as everything else. I would like to
supress that somehow. Any thoughts on that?

Thanks!