Results 1 to 5 of 5
  1. #1
    Aschwenker is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    1

    Advice on building a database to locate certain boxes in storage

    I am needing advice on how to build a relational database to easily track where a certain box is here at my work. We have a lot of boxes that contain financial records, work order numbers, and paperwork that is defined (certifications). I have made three separate tables and they all work great on their own for data entry. My issue is all these boxes are stored in one room, on the same shelf, and hold very little in common besides the shelf they sit on. My end goal here is to be able to have a report that shows 1) Where a particular box is, and 2) show who accomplished it.



    I have attached a picture to show what type of boxes are separated into each category. When I return to work tomorrow, I will check to see if this makes sense. I will be more than willing to show more if I can get some inspiration.
    Attached Thumbnails Attached Thumbnails Select boxes.jpg  

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,849
    Welcome to the forum.

    Very generally you have a A structure that is divided into Shelves and shelf areas. And a shelfArea that can contains 1 or Many Boxes where a Box can contain 1 or Many Items. We don't know what's in a box so identifying the contents to easily find an Item amongst Items. Are the Items in a Box stored inany particular fashion? If so, that would be taken into consideration as well.
    It appears your shelves are identified by ShelfArea eg 2J.
    Do the boxes get moved from Shelf o Shelf or from ShelfArea to another ShelfArea on same or different shelf?

    Do you have any test scenarios that indicate the types of things you would be retrieving?
    What exactly is the purpose for this application/ Who is the ultimate user?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,069
    Looks to me like one table ought to be enough. AutonumberID | Position | BoxNumber | etc. A field for contents description would make sense, otherwise you have to go there to know what's in any particular box. I don't see much in the way of unique identifiers for the boxes and that would be a must.

    What prompted you to make 3 tables for this?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,849
    My post was a generalization of what the picture and text said to me. I see shelves -multiple rows and multiple columns - with multiple boxes in each section. That's the abstract picture to be moulded by the details and examples only known by the designer and his/her environment. The location of a box does not seem to be the big picture to me (and I recognize I could be wrong). The boxes contain "something" and the something may be (best guess/worst case) the important part of the application. I'm waiting for the ultimate intended use of the proposed database.
    One table could/might accommodate the need, but that may represent the "shoebox" method of storing family photos.
    Requirements, facts and some test cases will carve away at the abstraction and lead to a design.

  5. #5
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    283
    Hi

    My initial thoughts are that:-

    Box No Yr2013 is located on Shelf 1J and Contains many documents

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

Similar Threads

  1. Update query not working and need advice on building my database
    By JEANNINEMFRANZ in forum Database Design
    Replies: 3
    Last Post: 02-06-2020, 08:52 PM
  2. Need Advice re: Building Database
    By musicmanbob in forum Database Design
    Replies: 30
    Last Post: 02-20-2017, 01:35 PM
  3. Replies: 12
    Last Post: 09-18-2015, 02:20 PM
  4. General Storage Advice
    By GraemeG in forum Access
    Replies: 1
    Last Post: 03-13-2011, 02:01 PM
  5. need some advice building DB (noobie)
    By sureshot in forum Access
    Replies: 2
    Last Post: 10-12-2009, 09:49 AM

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 - Senior Forums