Help with database design for archives inventory - Microsoft Access 2016


  • (Previously posted on Microsoft Community - moderator suggested I post here instead)

    Disclaimer: Access Noob (Been tinkering with it for less than a month at work with help from articles and tutorials)

    Hi everyone,

    I'm an intern tasked to refine an inventory survey form for a repository. The original design has each field in its own table, like, FormatHousingMedia, Additional Media, Condition Assessment, Recommended Treatment, etc.

    I would like to revise how we account for multivalued fields, like Media, Condition Assessment, Recommended Treatment. One record in the repository can have various media and various types of damages. The current form uses a checklist combo box for this, but this doesn't look like a good idea. When we export to excel for example, the values in the Condition Assessment field are separated by commas and there are very many different combinations of values.

    Do let me know if you have an idea how this database should be designed... I would appreciate it very much, thank you!

    P.S. New to Access

    For more information, the fields needed in the inventory form are

    Keyed In

    Accession number (but it references the main inventory which includes location, title, source, etc.)



    Date of survey

    Chosen from a combo box, likely to be only a single value


    Primary support

    Secondary support

    Likely to be multivalued


    Condition assessment

    Recommended treatment


    Housing condition assessment

    Housing recommended treatment


    Additional media

    Foreign object

    Foreign object condition assessment

    Foreign object recommended treatment

    Tuesday, June 19, 2018 12:49 AM

All replies