Results 1 to 4 of 4
  1. #1
    Haz is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    2

    Many to Many, many services with many managers

    Hi there,

    I have created a fully functioning database that collects incident information.

    Really my question is regarding best practice and design.

    within my access database I have used multi value fields to assign managers to multiple services. I am transferring to a hosted platform and it struggles with my design.


    So, I have 140 services. A manager has multiple services and each service has multiple managers, 60 managers in total.

    Ideally I would use a web form to add new manager and assign what services they had access to.

    I have this working perfectly when I assign multiple managers to a service, I just cant get my head around assigning multiple services to a manager without using multivalued fields.

    I understand the concept of many to many and they requirement for a third table.

    However I have spent the last 12 hours looking at this and think my brain has given up!!


    I just cant visualise this or make it work

    Any advice on how best to structure this would be great, wonderful in fact.

    Help!!

    Hazelle

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have used multi value fields to assign managers to multiple services.
    multi value fields only work in access - and only access 2007 or later. They should only be used for static lists (days of the week, months of the year, colours of the rainbow, etc), not dynamic lists like managers - they leave, move, get promoted.

    Ideally I would use a web form
    assuming you mean an access web app, these will cease to work next year. The only reason they are still available is to give developers time to rewrite their apps in another environment. If you do mean webforms, these are a visual studio object and do not support multivalue fields.

    Many to many tables are not difficult. At their simplest they contain two fields - a foreign key to the managers table and another foreign key to the services table.

    have a mainform/subform arrangement.

    To assign services to managers, the main form is based on the managers table and the subform on the many to many table - only one field needs to show in the subform and that would be a combobox based on the services table. Make sure the subform control linkchild and linkmaster properties are completed - manager foreign key in many to many table for linkchild and manager primary key in manager table for linkmaster

    To assign managers to services, the main form is based on the services table and the subform on the many to many table - only one field needs to show in the subform and that would be a combobox based on the managers table. Make sure the subform control linkchild and linkmaster properties are completed - services foreign key in many to many table for linkchild and service primary key in service table for linkmaster

  3. #3
    Haz is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    2
    thank you so much for your reply

    I am transferring my database to caspio.

    So, I think I understand the table structure in theory, I have this set up, I am trying to set up form but still don't get how I assign more that one to each manager.

    for example, I add new manager, email password etc, normal fields, then I come to service in subform with drop down, there can be 15 services assigned, do I enter 15 times for one manager? How do I know if a manager has already been allocated?

    I think once I can visualise this form in practice I will be away.

    Thanks a million :-)

    Haz

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am transferring my database to caspio.
    if you have the budget, it is not a bad system. Note it uses sql server, which does not use multivalue fields. I'm not sure it supports continuous forms either.

    With regards your questions, I can only respond in respect of access.
    do I enter 15 times for one manager?
    you select 15 services, no 'entering' required.

    How do I know if a manager has already been allocated?
    if you are saying a service can only have one manager, this would be dealt with in the rowsource of the services subform - excluding services already allocated a manager. and/or it can be managed through indexing, not allowing a duplicate index for the same service, or even same service/manager.

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

Similar Threads

  1. Working with Different Order Services
    By francogaspari in forum Access
    Replies: 2
    Last Post: 06-18-2014, 09:22 AM
  2. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  3. SQL Server Reporting Services
    By RayMilhon in forum SQL Server
    Replies: 0
    Last Post: 05-13-2013, 04:16 PM
  4. exposing Access / web services
    By gregnell00 in forum Access
    Replies: 1
    Last Post: 09-06-2011, 04:06 PM
  5. Sharepoint Access Services References
    By is49460 in forum SharePoint
    Replies: 0
    Last Post: 11-09-2010, 09:42 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