Results 1 to 8 of 8
  1. #1
    DataWarrior is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    4

    Inventory Database Items

    I have an Access db that items are entered in as a pallet or 2500. Each item is individually uniquely consecutively numbered and are packaged in boxes of 100.
    The pallet is shipped to a location that issues the items in the boxes of 100.
    I need to determine how to inventory the various offices.


    I also need to configure the db to issue the items in blocks of 100.

    I appreciate the help.

  2. #2
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Welcome to the forum!

    Have you started the database already? Hard to foresee what you want as the end goal based on what you have given us here.
    It sounds as though you are having a tough time getting the database started. I would suggest looking at the templates MS Access has as a part of the program, or searching online for one to suit your purposes. What is it your database is going to do? Who is going to use it?
    So far all I have gathered is you need something to store boxes/pallets of a product... but what do you need to do with this data?

  3. #3
    DataWarrior is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    4
    Thanks for the Welcome,
    I already have a db table built and the pallets of 2500 are already being entered.
    The office receving them issues them in boxes of 100.
    I need the db to keep track of how many are in the table and subtract 100 or the number of items issued from the total along with the items numbers.
    There are several offices and this will be done for each office.
    I have looked at several examples but none of them can or will track by item number.
    It was suggested to have a process that reads the 2500 and the start number of the items and add rows to the table but i dont know how to do that or if that is feasible.

  4. #4
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    So if I am understanding this correctly:

    You have a pallet of 2500 products, and they are distributed into boxes of 100 products each box. So there are 25 boxes of product per pallet.
    Does each box of 100 items have a specific, unique product ID? Is each pallet given a unique number/identifier?

    What is the databases job? Is it to keep track of (per each pallet) how many products have been sorted into a box of 100?

    Can you show us/attach what you have started already? (remove any private data, compact & repair db and zip file).

  5. #5
    DataWarrior is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    4
    Quote Originally Posted by nick404 View Post
    So if I am understanding this correctly:

    You have a pallet of 2500 products, and they are distributed into boxes of 100 products each box. So there are 25 boxes of product per pallet.
    Does each box of 100 items have a specific, unique product ID? Is each pallet given a unique number/identifier?

    What is the databases job? Is it to keep track of (per each pallet) how many products have been sorted into a box of 100?

    Can you show us/attach what you have started already? (remove any private data, compact & repair db and zip file).
    We are getting close. Yes there are 25 individual boxes with each item having its own unique number and they are sequencially numbered in each box with it's own ID.
    Each pallet is not uniquely numbered but it has the 2500 items start and end numbers printed on the pallet. I guess that makes it unique.
    When it is shipped the location it is shiped to and the 2500 start and end numbers are fields in the db.

    Currently the db stores the data about the items in the pallet. What I want it to do is keep track of the items removed from the pallet and the remaining items on the pallet.
    This should allow me to provide inventory of remaining and shipped items.

    I am in the process of rebuilding this db so instead of showing how bad my process is, I would be willing to start fresh if you have a solution.

  6. #6
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    1. I think you would want a table for each item. It would have the PK be its item ID, a field for the box number that the item is stored in, and any other fields you need associated with that item.
    2. Then a table for the boxes. Each box you said has its own unique ID so that will be this table's PK, you will also need a field for the pallet's primary key (see #3 below) for storing what pallet the box belongs to. Each item is related to one box, but one box may be related to many items. This indicates there will be a one-to-many relationship from box-item.
    3. Finally I think you would want a table for the pallets. I propose two possible ways of organizing this table. You can either
    A) have the primary key of the pallets table be an autonumber (it will just increment from 1 every time an entry is made into the table), which has no real significance, but is a way of organizing the table, and then have a field for the number of the first item in the pallet, and a field for the number of the second item in the pallet to keep track of which pallet is which. OR
    B) Have the primary key of the pallets table be the first item's number concatenated with the second item's number (maybe separated by a - or something, however you see fit). I would personally pick this option. However, this way I foresee no way to automatically populate this field, where as with option A you can set it so all fields are automatically populated if the requisite information is known.
    Then, for the pallets you will need a field for what boxes are in it (the box #). Then there will be another one-to-many relationship for pallet-boxes. (i.e. there is one pallet than contains many boxes, and any given box can belong to only one pallet).

    Here is a screenshot of what I would have my relationships set as given information above:
    Click image for larger version. 

Name:	relationship.png 
Views:	23 
Size:	15.1 KB 
ID:	23284
    The tracking of the number of item left in a pallet can be performed by a query. Every time a box is removed from a pallet you can append the field numBoxesLeft to subtract the number of boxes from 25. Hope this helps.

  7. #7
    DataWarrior is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    4
    I'll give it a try and see what happens.

    Thamks for the help and direction.

  8. #8
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Alright, see if that helps you get a start and what you can do after that. Any follow up questions post back here or feel free to start a new thread for your concern!

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

Similar Threads

  1. Add all line items to inventory
    By saudkazia in forum Programming
    Replies: 8
    Last Post: 10-12-2015, 09:38 AM
  2. Creating a repair history for inventory items
    By skiskiacm in forum Database Design
    Replies: 1
    Last Post: 04-23-2013, 07:18 PM
  3. Replies: 1
    Last Post: 11-26-2012, 01:11 AM
  4. ABC Inventory Database
    By pensacolajoe in forum Database Design
    Replies: 0
    Last Post: 09-05-2012, 02:35 PM
  5. Replies: 3
    Last Post: 05-15-2011, 10:52 PM

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