complicated relationships in a single report

[samiuweb]

hello,
i'm totally new to this forum, and fairly new to access but i'm gonna send a message just to give it a try....

here is the problem... i have a database with a central table "Projects" and 4 linked tables: Students, Professors, Organisations and Departments. Each of them is in a many-to-many relationship with the projects table (so there is one more table for each that joins each of them with Projects). This is bcoz we can have 1 Project with 2 Students, or the same student with 2 Projects (same happens with the other fields).

What I'm looking for is a general report sorted by Project, showing all students, depts, profs and organisations that are linked to that specific project.

Any easy (or not so hard) ways of doing this?

thx
pm

 

[llkhoutx]

Just build a query, linking all your tables, and use that as a recordsource for your report.

 

[samiuweb]

alrite,
i tried it and it seemed to work, but there are a few glitches.

first of all, i have some Projects in the database linked to multiple professors, organizations and students. In the report, I get duplicates for each of these fields (ie. for a project with two professors, same student name would show up twice).

i tried using the no duplicates filter in the report format, but i figured it doesnt help me. since profs or students or organizations can have more than 1 project, the no duplicates thingy wont show the name of the prof on the second (or third and so on) project to which he is linked to.

anyway, to clarify what im lookin for, let me me rephrase it. i need a report of all project records to show their respective linkage to professors, students and organizations... the latter need to show up only once per project, but there could be duplicates when profs, students and organizations are linked to more than one project (they would show up as many times as projects they're lnked with).

thanks everyone,
penar

 

[llkhoutx]

Add "grouping" and/or "right joins" to your queries to eliminate duplicates.

 

[Pat Hartman]

It is NOT possible (ok so it's possible, but it is not reasonable) to make one big query out of these separate relationships. Students are not related to Professors. Professors are not related to Departments. It makes NO sense to combine them in a single query even though they all have a common field - projects. The solution is to make a report based on project and then use a separate subreport for each of the different types of related data. So you would have a main report with FOUR subreports.