Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Location
    Stansted UK
    Posts
    5

    Unanswered: Help in designing tables and form entry for multi selection of fields

    I have a database that holds information regarding our contractors. Each contractor has a licence which allows them to work on specific types of aircraft and the level to which they are approved to work.

    The current form works perfectly well to record the aircraft type and the approved level of competance, but I now need to add any limitations to the level, so for example:

    B747-8 B1 1,3,6

    B747-8 is the aircraft
    B1 is the level of compentance
    1,3,6 are the limitations to the level of compentance

    There are 10 different limitations, each of which can be used seperately or in combination with each other:
    1,2,3,5,6,7,9,10,11,XX

    In addition, the limitations may be ammended or increased in the future.

    How do I set up my tables and my form to allow both for the selection of several limitations in any combination and so that it can be easily ammended in the future?

    I have tried using the multi select list box, which I know many purests don't like, but it doesn't work anyway - I have a split database and it causes the db to crash when I use a linked table as the source of the list box.

    My current tables are:

    tblContractors
    tblLicence
    tbl145Limitations
    tbl145JUNC

    tbl145Limitations has the limitation number and the description as records, rather than fields because that way they can be ammended in the future. But I can't work out how to then use them as fields in tblLicence.

    I have also created tbl145JUNC as a junction table to allow a many to many relationship, but the above still applies.

    This one is simply beyond my competance, so thank you for your combined expertise. Hopefully you can help me avoid spending more time tearing my hair out...

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    How have you linked the two main tables to the intersection (junction) table?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it were me I'd look at a design simialr to the attached PDF
    Im assuming that a contractor must have a specific skill set (competancies for a specific aircraft)
    ClientCompetencies defines that a specific contractor has a specific competency for a specific aircraft type

    you would need to define a new row for every competancy for every aircraft type for every contractor

    Limitations defines the generic limitation category

    ContLimtatiosn defines the limitations applicable to a specific contract/aircraft/competancy


    for presentation you probably want something that would roll up the contlimitations in, say a comma separated list as you are used to in your paper system
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2013
    Location
    Stansted UK
    Posts
    5
    @weejas
    Relationships are as follows:

    tblCONTRACTORS (one) - (many)tblLICENCE

    tblLICENCE(one) - (many)tbl145JUNC(many) - (one)tbl145LIMITATIONS


    @healdem

    Thank you for your help and for taking the time on my thorny problem. I need to spend some time on your suggestion, so will come back to you when I've been able to do so.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •