Many to Many relationship

[luizamorim]

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?

[Mathilda]

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.