Generate a Report(Worksheet) from three Tables in a N:M Relationship

**amq** · 09-12-2012, 03:51 PM

Hi everyone,

even though I somewhat feel I am raping Excel into doing something it was not intended to ever be doing here is what I am trying to achieve:

I want to create a report(-like worksheet) that is populated with data from three different tables looking like this

Problem_Table
PB_id | PB_Descr
pb1 | baad
pb2 | really bad
pb3 | not that bad

Project_Table
P_id | P_Descr
p1 | make everthing better
p2 | optimization
p3 | relax

Matching
PB_id | P_id
pb1 | p1
pb1 | p2
pb2 | p1
pb2 | p3
pb3 | p3

So one problem can have multiple "solving" projects while at the same time a project can tackle multiple problems.

What I guess I could do is to write a VBA looking somewhat like this

foreach pb in problem_table
 copy_over(problem_table.pb, problem_table.descr)
 foreach matching.pb in matching
  if matching.pb = problem_table.pb then
   foreach project_table.p in project_table
    if matching.p = project_table.p then
     copy_over(project_table.p, project_table.descr)
    end if
   next
  end if
 next
next

(this is not actual vba code as you might notice, but it should come close to it ;-) )

The problem with this is...
1) that it's ugly
2) that I am afraid my pc will explode while calculation (aka its slow)
3) that whenever some extension of this is needed I am the only one who can fix it (ew)

Is there any SMART way to get a report with such a N:M relationship with three tables?

Thank you so much for your help!

Best regards,
Andreas

**protonLeah** · 09-12-2012, 04:13 PM

This post looks like one some one did for you previously: here.
-----------------------------------------------This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

Have you started any work on this project yet?
How far did you get?
Where are you stuck?

We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.

**amq** · 09-13-2012, 02:34 AM

Hi and thanks for your response,

I finished the other projects that I posted about and was thanks to the generous help of the members here able to teach myself how to do that. Hence I have the entire copy routine that I described above lying in a module, ready to apply it. If you want me to post that I can surely do that.

This question however targets more the "I don't want to use a slow ugly alfoythm if there is a smarter way to do it" part.

As you might have guessed from the title and maybe even the problem description I would usually try to do this in MS Access' reporting function based on a query that left joins on problem and groups by problem.

Is there a way to do this in excel?

Thank you!

**amq** · 09-13-2012, 03:28 AM

Just a quick add: The question I had before was concerning a N:1 relationship making it somewhat easier to tackle than the N:M relationship I am thinking about now.