Results 1 to 3 of 3
  1. #1
    andrewb is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    2

    Question on database structure - limiting linked records

    Hi all, sorry for the ambiguous and possibly incorrect thread title but I had no idea how to briefly describe my challenge.

    Currently I have a database with the following relationships:

    One Client has multiple Entities
    One Entity has multiple EntityServices


    One Service has multiple EntityServices (basically a table of service lines)

    Now this is all working fine, but I want to create a table by the name of EngagementLetters. It should have the following relationships:

    One Client has multiple EngagementLetters
    One EngagementLetters has multiple EntityServices

    and the tricky one: all EntityServices under a single EngagementLetter must have the same Client

    I've been able to create the above, but not the last point. It's stumped me, I have no idea how to do it! Would anyone be able to help with this?

    Thanks in advance!

    Cheers,
    Andrew

  2. #2
    Estuardo's Avatar
    Estuardo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Portugal
    Posts
    22
    G'd morning Adrew,May be i don't understand what you want, because i see no problem to add a foreign key from the table EngagementLetters to the Client Table, and a foreign key from the EntityServices table to EngagementLetters. Take a look at this:
    Code:
     
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    Click image for larger version. 
    
    Name:	relationships_andrewDb.JPG 
    Views:	6 
    Size:	37.1 KB 
    ID:	6820
    With this design you just have one drawback. You will need to join 4 tables to get the result you need.G'd Luck

  3. #3
    andrewb is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    2
    Hi Estuardo, thanks for your reply. However I'm a bit confused with your above solution as it doesn't seem to fit the structure I described. For example, in my structure each client must have multiple entities (think client = company group, entities = companies), but with your solution a client has only one entity, but an entity can have multiple clients. So thanks, but it's still not the solution I need.

    EDIT: Here's what I currently have:

    Click image for larger version. 

Name:	Design.jpg 
Views:	3 
Size:	21.6 KB 
ID:	6850

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Question on how to structure a database
    By ExterminatorJeff in forum Database Design
    Replies: 3
    Last Post: 03-17-2012, 06:34 AM
  2. Form limiting records
    By yaro.kobzar in forum Programming
    Replies: 1
    Last Post: 05-21-2011, 06:50 AM
  3. limiting amount of records
    By yaro.kobzar in forum Programming
    Replies: 1
    Last Post: 05-17-2011, 07:01 PM
  4. Replies: 1
    Last Post: 10-22-2010, 01:29 PM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums