none
Help with database design for archives inventory - Microsoft Access 2016

    Question

  • (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.)

    Width

    Depth

    Date of survey

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

    Format

    Primary support

    Secondary support

    Likely to be multivalued

    Surveyor

    Condition assessment

    Recommended treatment

    Housing

    Housing condition assessment

    Housing recommended treatment

    Media

    Additional media

    Foreign object

    Foreign object condition assessment

    Foreign object recommended treatment


    Tuesday, June 19, 2018 12:49 AM

All replies

  • Hi Ziqs,

    In your scenario, you want to create database in MS Access. Right?

    Since this is a forum which is talking about SQL Server Database design. I think you need to post your question to MS Access forum for more professional help.

    https://social.msdn.microsoft.com/forums/office/en-us/home?forum=accessdev

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 20, 2018 6:30 AM