Reverse relationships

[PapaBear]

Hi all,



I need to relate two tables to each other so that when I enter the value of one cell in table A then it cross-references it and automatically updates the values in table B.

Perhaps, it is easier to explain by the below example. Here I need to enter in Table B each of the deals that have occured over a given period. In the column "Representatives" I need to specify which sales persons were associated with the deal so it needs to refer to Table A column "RepresentativeName". No problem!

The problem, however, is establishing a "reverse relationship" so that relevant sales persons ("RepresentativeName" in column A and "Representatives" in column B) are related to the respective deals.
I.e. for each of the respective sales persons that I enter for each deal in Column "Representatives" in Table the same sales persons need to be associated for those respective deals.

I hope that I have explained in sufficiently.

Many thanks, Jacob

Example

Table A: Contacts
- RepresentativeName
- Company
- ContactInfo
- AssociatedDeals

Table B: Deals
- DealName
- TotalValue
- Representatives
- Comments

[John_G]

Hi -

Before you go further, I have one question:

Can any one deal have more than one representative associated with it? If the answer is yes, then you have a many-to-many relationship between the "Deals" and "Contacts" tables, and you will need another table.

I suggest that you use unique ID fields (usually numeric) as keys for your two tables, to identify the Representatives and Deals, rather than using names.

John

[orange]

These materials may be helpful

http://support.microsoft.com/kb/304466

[PapaBear]

@Orange: Many thanks!
@John_G: Yes, any one deal can have multiple Representatives associated with it and any one Representative can be associated with multiple deals. The two tables already have a unique key.
Still, my main "problem" is that I want my tables to autoupdate. For instance, if I in Table A under a given sales person select Deal A, C and G, then I want Table B to automatically update the given sales person as a representative on deal A, C and G. Also, it should work vice versa so that when I associate a given deal in Table B with Representative 1, 3 and 5 then it should automatically add that deal to sales person 1, 3 and 5 in Table A. Is that possible?

[orange]

You may also find this video helpful in resolving many to many relationships.
https://www.youtube.com/watch?v=7XstSSyG8fw

[alcapps]

I did this for someone else but it gives you an idea of the many to many relationship

employee to project.

one project can have many employees
one employee can be on many projects.

check it out you can use this for your purpose as well.

check out the many to many table in the db. employee2project
test.mdb

[PapaBear]

Thank you all.

@orange: great, very helpful! However, I need the two tables to autoupdate, and preferably so that they show as multiple values in one cell as you can do using the lookup wizard. Is this possible?

@alcapps: Unfortunately it doesnt seem to work when I open your database. Im using 2010 myself - perhaps this could be a reason?

[alcapps]

dbtest.mdb

try this db I removed the code module that may have gave you an error. It was not being used in the example. If you have a problem with this one try to open a module and check references and fix them and you should be ok. I think this one will work for you.

[orange]

I do not use, and do not advocate using multivalued fields. It is a "feature" introduced by M$oft, but in my view violates good database practices.

[PapaBear]

@alcapps: It works now - unfortunately there is only one table in the database?

@orange: Thank you for the advice. However, this is a very simple database which is not used for a broader purpose. What difference does it make if I use multivalued fields or not?

Perhaps, I dont need multivalued fields? In the end I simply want to be able to run queries so that it returns a list of all the diferrent sales representatives and their associated deals and, similarly, run a query on all the different companies to see who work for that company.

Really, what I need the most is for my tables to "know each other" so that I when I in Table B add sales person #1, #4 and #5 for Deal A then it automatically adds Deal A to these sales persons in Table A. Is this possible?

[orange]

In this case if you have something that works and you're happy -- great.

However, if you want to learn how to design tables, create relationships and use common techniques such as Normalization, then nothing teaches you more than building a database that you have a vested interest in.

Here is a link to a generic process to design and build a database. You could use this to rebuild your own, or just follow the example to see how things evolve.

http://www.rogersaccesslibrary.com/T...lationship.zip

[alcapps]

I just opened the version on the website and it had 3 tables and 4 forms in the database. Not sure what is wrong.

Al