Results 1 to 3 of 3
  1. #1
    lewis1682 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    37

    Bag Stock Management System - DB Design?

    Hi Everyone,

    Been a while since I have actually sat down and made a Access DB.



    However the time has come again. I needing to create a DB to help me manage some stock. The stock can be stored in multiple locations and requires minimum stock levels depending on the location. All most all items also have a expiry date which I need to keep an eye on. This system will be used to keep First Aid bags up to the required levels, and all items in the bags must be within the expiry date. For simplicty I want to focus this post on only 2 bag types (FA & AFA). So far I have created a table which contains every type of item and the minimum stock requirements for each storage location (bag or stock room)

    ID Item_Name Item_Min_Ammount_FA Item_Min_Ammount_AFA Item_Min_Ammount_Amb Item_Min_Ammount_Stock Item_Category
    1 Nitrile gloves (medium pair) 6 6 0 0 Infection prevention and control


    What I am struggling with is how to design the database where a whole list of items can be assigned to each bag, and keep track of the individual stock levels and expiry dates?

    All input is welcome.

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you're struggling because you don't understand normalization, or at least, you're not applying it here. What you show looks like a spreadsheet, which is column based data layout while Access is row based. You might need tables for bag types, items, locations, bagItems and bagLocations - the latter 2 being junction tables. The old maxim is worth repeating - if you can't design it on paper so that it will work, you can't build it in Access. In other words, you need to be able to put your vision on to paper first. Maybe start here

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...ng-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    or find other sources that you like better. You could post back with your suggested design or just go for it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron's comment and his links.
    If you stand back, I think you are saying
    --in a number of Locations, there are
    ---a number of Bags, each with
    -----a number of Contents (FirstAid related Items) that have an ExpiryDate.

    Details of your business processes will help define exactly how you identify Items or Bags to be dispersed when needed.

    So, in this approach, I see 3 (at least Tables)
    Location has 0,1 or Many Bags and each Bag has 1 or Many Items.

    Does this help?
    Another underlying issue in your set up is using the oldest Products to minimize the number of Items that pass their Expiry date and become "scrap/disposed items". This is first in first out (FIFO), but I suggest you focus on your tables and how they relate one to the other.

    There is a link to Database Planning and Design in my signature that has many articles that you may find helpful.

    I would not design with 2 Items in mind. Design for the general case --any number of Items; then you may focus your testing with only a few cases (2 Item types) to make sure your design works. But it should be tested with many scenarios -good and bad- to reduce future problems/design defects. See this stump the model post for some testing/design iterative ideas.
    Good luck with your project.

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

Similar Threads

  1. Replies: 5
    Last Post: 03-14-2019, 03:01 AM
  2. Replies: 4
    Last Post: 10-01-2016, 04:15 PM
  3. Replies: 2
    Last Post: 10-04-2014, 01:58 PM
  4. Creating a form for stock management
    By StevenStip in forum Access
    Replies: 3
    Last Post: 05-06-2013, 01:00 PM
  5. Product / Stock Management
    By dale.90 in forum Access
    Replies: 1
    Last Post: 09-30-2010, 11:08 AM

Tags for this Thread

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