Many to many relationship on a report

[Lois]

Hi


i have a report which i want to show data that is part of a many to many relationship (different item Nºs & QTYS). how do i get it to show each Item nº & qty per customer?

thanks

[alansidman]

If you have a many to many relationship, you will probably need to have an intermediate table, called a junction table. Here is an excerpt from MS Knowledge Base on Many to Many relationships

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key).

HTH
Alan

[Lois]

Hi
i have the tables set up correctly for a many to many relationship, but how do i show the 'many entries on the report???

[alansidman]

You will have to post more information or post a copy of your data base. In either event, indicate an expected outcome of what you would like your report to look like and show. Not enough information to guide you on what you need to do here. What is the recourd source for the report, table or query? field names? etc.

[Lois]

Hi,
is there any other way i can post my database other than through the manage posts as it always says the file is too big...

[alansidman]

Suggest you run a compact and repair. If that does not make the db small enough, then you can zip it and it will probably fit. If that doesn't work, then make a copy and only include sample data showing a representation of your informtion. If you do that, run a compact and repair again before uploading.

One last thing, many users of this forum are still running AC2003 (.mdb). To get maximum visibility, suggest you save your file as an AC2003 or earlier version before uploading.

Alan

[Lois]

Ok i have managed to upload it now..
the report is the urinal manufacture sheet & i need each item & qty in the job item link table to appear in the body of the report...
thanks

[alansidman]

I created a sub report of the Parts and Qty and inserted it into the primary report. You can change it up as necessary. I linked the main report and sub report on the Job No. This is similar to setting up a sub form in a main form.

Post back with your questions.

Alan