473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Update reverse cross reference?

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!

Aug 20 '07 #1
4 1707
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!
Aug 20 '07 #2
On Aug 20, 10:41 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
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

Aug 20 '07 #3
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!

Aug 20 '07 #4
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!
Aug 20 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Art | last post by:
Can I update Application("SomeVariableName") on server A in response to user's action on server B? There is no problem in updating the same variable on server B, of course, but we would like both...
1
by: C.P. | last post by:
I am trying to transform data inside one of my tables. However, I only want to change part of the data in a column because the last part is basically a UniqueID to a file. Is it possible to only...
8
by: xiao zhang yu | last post by:
me was sorry if this question are present before DotNet, no matter VB.Net or C# all they are compiled to IL, and yes, that IL will totally same as "open-sourse", every IL will easy to decompile...
5
by: Chad | last post by:
Could any suggest to me a good way to programmatically identify which SPs update a database column. I would like to create a cross reference for our database.
3
by: Lapchien | last post by:
A user has a make table query, basically it does some summing and creates a 'summary' table. I'd like to get rid of it (it's the last one in the access back-end) and use pure sql. Is the same...
5
by: Dave Smithz | last post by:
Hi there, Been working on an evolving DB program for a while now. Suddenly I have come across a situation where I need to update a table based on a group by query. For example, I have a table...
20
by: mike7411 | last post by:
Is there any easy way to reverse the order of the bits in a byte in C++? (i.e. 00000001 becomes 10000000)
1
by: sunnyluthra1 | last post by:
Hi, I was creating an Application in MS Access for Geocoding a particular Address from Google to get the Lat & Long. I successfully able to did that. Here is the code:...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.