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

  • Thread starter Thread starter Tegan
  • Start date Start date
T

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)
 
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...
 
Back
Top