Indirect 1>M relationships

[jnew]

Greetings,

I'm having trouble joining records in three tables for a
community college. Table1 has class section info. It is
related to Table2 which has instructors & wages assigned
to each section. One section can have multiple
instructors. Table1 is also related to Table3 which lists
rooms assigned to each section.

Here's the problem. Since tables 2 & 3 aren't directly
related, I'm having problems getting the query output I
need for reporting purposes. For example, if a section is
assigned two rooms and two instructors, I'm getting four
rows of output for my reports. Below are the desired
output formats for various scenarios:

Scenario 1, two rooms, one instructor:

Section Room(s) Instructor(s) Wage
D01 RM A Smith, A $100
RM B


Scenario 2, one room, two instructors:

Section Room(s) Instructor(s) Wage
D02 RM A Smith, A $100
Brown, B $150


Scenario 3, two rooms, two instructors:

Section Room(s) Instructor(s) Wage
D03 RM A Smith, A $100
RM B Brown, B $150

How do I create a set of queries for my reports that will
combine data as I've illustrated above? I've tried
building a main report with side by side subreports, but
get the same results. Any help will be appreciated.

jn

 

[[MVP] S.Clark]

I think the answer is to create a query to combine all of the data into one
big query, then use the available tools and properties within the report
designer to format it as needed.