Jump to content
  • 0

Many to Many relationship


luizamorim

Question

Hello,

 

I am developing a report result based in two tables: the first one is holding the 'clients' with unique IDs, and the other one have the 'products' also with their IDs.

Both lists have details, however the clients have special columns which indicates the products they have. For example, the 'client 1' has 3 more columns because he has the product 1, 24 and 33. The clients may have up to 9 products each.

The report should be generated by searching for the name of the client, listing his products.

The thing is that I don't know how to generate this report, it just shows the 'product id' on the results, which should be replaced for the 'product name' instead.

I thinks the best way to do it is a many to many relationship, because the clients can have more than one product each. I've read something about making another table which would link all the information as a many to many relationship, but I am not sure how I can list the results from an "empty" new table.

Is there any other way to do it?

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0
On 1/2/2017 at 0:58 AM, luizamorim said:

Hello,

 

I am developing a report result based in two tables: the first one is holding the 'clients' with unique IDs, and the other one have the 'products' also with their IDs.

Both lists have details, however the clients have special columns which indicates the products they have. For example, the 'client 1' has 3 more columns because he has the product 1, 24 and 33. The clients may have up to 9 products each.

The report should be generated by searching for the name of the client, listing his products.

The thing is that I don't know how to generate this report, it just shows the 'product id' on the results, which should be replaced for the 'product name' instead.

I thinks the best way to do it is a many to many relationship, because the clients can have more than one product each. I've read something about making another table which would link all the information as a many to many relationship, but I am not sure how I can list the results from an "empty" new table.

Is there any other way to do it?

Hi,

As far as I understand one product can have many clients and each client can have many products.

You need to create one more table to build a many to many relationship. Build a one to many relationship between client table and the 3rd table based on client ID and one more one to many relationship between product table and 3rd table based on product ID.  So you will get a many to many relationship in the 3rd table. Then you may create a view using 3 tables and display needed information on report.

Please feel free to ask if you have questions.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...