Results 1 to 5 of 5
  1. #1
    RaSk is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    2

    Beginner - Many-to-many relationships

    Hello,

    First of all sorry for my English I'm French and I'm completely new to Access but I have worked for a few years on Excel. I've finished reading a book on how to begin to design a database but it's the first time I have to design it for real!
    To describe the scope of the project, here's the goal for each one in my company :


    • Manager/Business developer (don’t want to work with Access) :



    o Update their budget Excel file with all active contracts (an active contract during July 2015 can be counted as a potential contract for 2016)

    • Suspension tech (can work with Access) :

    o View all suspension (including historical) and add/modify them

    • Assistant (can work with Access) :

    o View all groups/clients (inc. historical) and their respective contracts (inc. historical) and their respective end-users. Maintain this information (can add and modify).
    o Maintain information about contacts, distributors and business developer of those groups/clients
    o Recap the invoices and have a global view about what is left to invoice
    o Maintain information about overdue (for example historical comments for each overdue)

    I have managed to design tables and relations for suspension, contact and overdue aspects. I hope that I have done correctly but now I’m facing a major problem: linking invoicing, contract (with price detail) and end-users!
    I struggle to design correctly this part, maybe because of the many-to-many relationship when we start to talk about historical information!

    How do this work at the moment ?

    Open the Excel file enclosed –just ignore the budget sheet for now–. Each month the assistants copy the last existing sheet in the invoicing Excel file and update the invoice ID, the comments and change the color for the corresponding month. You have an extract of that file on the Invoicing sheet, as you can see it is the September invoice sheet as the September 2015 is already all green, it means that all the invoices were sent. The whole Excel file can retrace all historical invoices, we just have to switch sheets.

    My initial task was linking within Excel the budget files with the invoicing files but to be honest it’s a real mess: each business developer has his own budget spreadsheet stored locally and each assistant has her own invoicing spreadsheet –because of writing permission issues, if we joined all files together they would be 3 working on the same excel file–… so I thought about creating a relational database! But now I’m struggling with the most important part I think…
    The problem is: some clients want to be invoiced for several end-users, it depends on their contract, but we need to keep historical information about those contracts and even more important the price detail. It is a many-to-many relationship! As you can see on the Budget sheet, the manager needs another view of those contracts but it’s almost the same as the invoicing file.

    ExcelFileExtract.zipClick image for larger version. 

Name:	Table Design.png 
Views:	17 
Size:	136.7 KB 
ID:	22616
    EDIT: Cross-posted on Acces-Programmers forum

    Please feel free to correct my proposition for the tables design and give me advices for the missing part !

    Thank you,

    RaSk
    Last edited by RaSk; 11-03-2015 at 04:48 PM.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What I do is use a junction table that lists all of the contacts associated with a contract. So the table would have ContactID and ContractID. These columns will be indexed and allow duplicates. When a Contract is created, the User needs to select one or more contacts. These selections are appended to the junction table using VBA code. The ContractID and each of the selected ContactID's are appended.

    In your case, you have ContactGroups. So you may need a third column in your junction table to define if the ContactID is a group or an individual. Alternatively, you can merge your GropupContacts with the individual Contacts and make the distinction in the, new, merged table using a Yes/No field/column.

    On a side note, I would recommend using default values for your Foreign Key fields. Have them default to 0. Looking at your diagram, I might interpret it as allowing Nulls in some of the FK's.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rask,
    cross posted http://www.access-programmers.co.uk/...d.php?t=282078


    When you post the same or similar question in multiple forums it is called a cross post.

    Cross posting is not wrong, but does require some consideration of the readers ( unpaid volunteers).
    Please tell readers that you have cross posted -and - include link(s) to the other post(s).

    Here's why.



    Good luck with your project.

  4. #4
    RaSk is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    2
    Quote Originally Posted by orange View Post
    Rask,
    cross posted http://www.access-programmers.co.uk/...d.php?t=282078


    When you post the same or similar question in multiple forums it is called a cross post.

    Cross posting is not wrong, but does require some consideration of the readers ( unpaid volunteers).
    Please tell readers that you have cross posted -and - include link(s) to the other post(s).

    Here's why.



    Good luck with your project.
    Orange,
    Thanks for the tip, I've edited my post with a link to the other forum.

    Quote Originally Posted by ItsMe View Post
    What I do is use a junction table that lists all of the contacts associated with a contract. So the table would have ContactID and ContractID. These columns will be indexed and allow duplicates. When a Contract is created, the User needs to select one or more contacts. These selections are appended to the junction table using VBA code. The ContractID and each of the selected ContactID's are appended.

    In your case, you have ContactGroups. So you may need a third column in your junction table to define if the ContactID is a group or an individual. Alternatively, you can merge your GropupContacts with the individual Contacts and make the distinction in the, new, merged table using a Yes/No field/column.

    On a side note, I would recommend using default values for your Foreign Key fields. Have them default to 0. Looking at your diagram, I might interpret it as allowing Nulls in some of the FK's.
    Thank you for the recommandation. I'm curious of the advantages of your solution as I'm a beginner, is it faster or is it to avoid having a lot of unnecessary tables? I'd like to keep a table for clients but if this could be done by a junction table including client information and business dev/distributor/contact information too I'm good with it.

    And thanks your advice for the default to 0 in foreign keys that could be null!

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by RaSk View Post
    ... is it faster or is it to avoid having a lot of unnecessary tables? I'd like to keep a table for clients but if this could be done by a junction table including client information and business dev/distributor/contact information too I'm good with it....
    It is not an issue of using a junction table to replace a contacts table. I suggest you consider merging your GroupContacts with Your ClientContacts. Just have a Contacts table. In the Contacts table, include a column that will distinguish an individual contact from a group contact. It may be beneficial to have both in one table when it comes time to associate a contact with a Contract.

    For instance, if a contact is a GroupContact you can assign a value of False to a Boolean. In Access, you can add a Yes/No data type as a column in a table. The fields within this column will behave as a Boolean. You can assign True or False to it. If I had a Yes/No column named IsClient, for the records that are a Group I would assign False to IsClient.

    To address the issue regarding your question of what to do about your contracts and relationship to the contacts. I would entertain the idea of a Junction Table.
    What I do is use a junction table that lists all of the contacts associated with a contract. So the table would have ContactID and ContractID. These columns will be indexed and allow duplicates. When a Contract is created, the User needs to select one or more contacts. These selections are appended to the junction table using VBA code. The ContractID and each of the selected ContactID's are appended.

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

Similar Threads

  1. Help for a beginner
    By Beecharmer in forum Access
    Replies: 1
    Last Post: 03-04-2014, 09:44 AM
  2. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  3. Beginner at relationships.
    By Juice118 in forum Database Design
    Replies: 6
    Last Post: 08-02-2011, 09:33 AM
  4. Help a DB beginner
    By adquinn in forum Access
    Replies: 0
    Last Post: 02-08-2011, 08:25 PM
  5. Need help! Access beginner here!
    By Joshin in forum Access
    Replies: 1
    Last Post: 06-09-2009, 05:12 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