Reporting on a many-to-many relationship 1

[Algernon83]

My database has two tables that share a many-to-many relationship. Call them A and B. There is, of course, a join table, which we'll call J. J has a foreign key for A and for B, as well as a 3rd value. I'd like to create a report that uses each foreign key in J as one of the axes of a table, with the value in each cell. Is this possible without a great deal of coding?

 

[dhookom]

How about providing some sample records and desired display in your report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Algernon83]

Sure. I'll keep it simple, with a 2x2 grid.

A
------------
a_id a_name
0 "foo"
1 "bar"

B
------------
b_id b_name
0 "sna"
1 "fu"

J
-------------------
a_id b_id j_value
0 0 0
0 1 1
1 0 2
1 1 3


The resulting table should be:

sna fu
foo 0 1
bar 2 3

 

[dhookom]

I would think that you could create a crosstab query that is comprised of all three tables with the appropriate joins.
Set a_name as the Row Heading, b_name as the Column Heading, and Sum of j_value as the Value.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Algernon83]

Thank you. That is precisely what I needed.