main form with many-many relationships (and intermediatery table) involved

[Tegan]

Hi,

I'm working on a database to track aspects of health campaign projects.
I do have a live version that works however I felt that splitting the
tables up further would be a better foundation. To explain:
The Projects table is the base of the main form
- Trim numbers (the files these projects are put onto - a Project can
have more than one, and a Trim can possibly have more than one Project
phase on it),
- Consultants (that work on the various projects),
- Reports (some of which relate to specific projects but some also
stand alone): (subform tab),
- Payments (invoices to Consultants for each project): (subform tab).
I also have some lookup tables (which need to be updateable by entering
into the table, ie not just a list within a combo box property tab),
one of which is Subjects (ie alcohol, drugs, HIV etc). This lookup
needs to apply to Projects but also to Reports - (as does the
Consultants table).

So eventually I created an 'ALL DETAILS' table that just housed the
primary keys giving a separate row for each combination of data from
all those tables.
The problem was, to have a main form with 'Project title', 'Trim no.',
'Subject', 'Consultant' on it, I needed to have a query with the ALL
DETAILS table in it to get the data linked through that table. And then
I get a separate record for every incident of Project with different
reports - I can't design a query for the main form that doesn't list
every individual incident of Project + Report (because I'm using that
ALL DETAILS table to get the other data - subject, consultant, etc)
Help!
Thanks, and sorry this is so long-winded!
(have attached diagram for illustration)

 

[Rebecca Riordan]

If I'm understanding you correctly, the problem is with your single
AllDetails table. What you need to do instead is create tables to resolve
each many-to-many relationship independantly. So, for example, ProjectTrims
and ProjectConsultants.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...