Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    How to handle inventory?

    Hi all,



    I'm creating a new system for a small company and I was wondering about options to handle inventory. I came across multiple solutions like having QtyOnHand in the product table, have a separate Inventory table and so on.

    Here is my situation:
    • We buy products from multiple suppliers
    • Products are both physical and virtual (software, services...)
    • Some products have serial numbers, some don't
    • Some products go into sales orders, some we take for our own needs (service, demo...)
    • Some products need to be marked as "reserved" for a particular sales order - if we are waiting for the rest of the items to be on stock
    • I want to handle minimum qty on hand for each product and have an alert that I need to order something
    • I want to be able to scan a barcode (EAN or serial number) and the system will show the details of that product/unit
    • I want to see when each unit was stocked
    • I want to work with FIFO (first in first out) principle - for serialized products only


    Can you please try to help me with the data model for this? What do you think will work the best?

    Thanks a lot.

    Tomas

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I'd start by Googling for an existing data model to work from, or even a DB template?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Yes, of course, but I would like a completely independent opinion from here, so I can make the best ultimate decision :-)

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Well, that would not be me, as I have never created an Inventory DB.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This is not a simple or small undertaking.

    Generally speaking you don't store a qty on hand, you calculate from a transaction record.
    The transaction type will indicate in or out, or stock take or a correction.

    Inventory is complicated, do you need to keep track of where it is within your warehouse?
    Can that be more than one place, how do you control where they are moved from or picked if you have multiple locations?
    Do you have a dummy location for non-physical items?

    You mention FIFO - this will need you to possibly include date stamps or batch numbers on your stock with appropriate labelling.

    Serialised stock adds another headache. It's often best to not record this until it's moved out as a final transaction record.
    Other wise if you have 200 widgets on the shelf do you tell the system you want serial number 365478 , and some poor person has to go and search for that specific serial number on 5 shelves of widgets...

    I would draw out on paper your business process, and then use bits of paper to work out what you need to capture and where.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    No, I don't need to track where within the warehouse.
    Just one location.
    For the non-physical items, like service for example, I prefer for them to be available at all times (infinite stock)
    For the FIFO, I imagine that I will be storing date and time when each unit was added to stock. Then I thought a DMIN function would be enough?

    I definitely need to store serial numbers from the beginning. Yes, the poor person thing is true, but we will have to live with that I'm afraid :-D

    How would I go about the transaction table? Something like this? I need to grasp the basic concepts behing the inventory, then I think I can go through my business process:

    Click image for larger version. 

Name:	d6.PNG 
Views:	45 
Size:	7.6 KB 
ID:	46423

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Tomas

    This http://www.allenbrowne.com/AppInventory.html will give you the basics.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What Mike posted is the first thought I had.

    Heres another site with data models http://www.databaseanswers.org/data_models/
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thanks, I looked into the database answers before. Unfortunately, I found the inventory data models very strange. There is usually QtyOnHand stored in a table, and it's very specific types of inventory, not exactly what I need.

    This http://www.allenbrowne.com/AppInventory.html will give you the basics.
    --- that's useful, thanks!

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some insight/help from this older thread.
    I agree with Mike that the Allen Browne AppInventory is a great reference.
    There are youtube videos discussing FIFO.

  11. #11
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thanks. I will check it out.

  12. #12
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Like most things in a database, everything is backwards. Start by thinking what you want out of it, define that output into column headings, put some real thing's under those headings. Now are any those things calculated or combined, if so separate them into individual items.
    Now pull some input sources (shipping orders, purchase orders, etc) and create more column headings and data. Is all the data needed here? If not, where does it come from? Is all the data in each column the same type ie. Date can be shipped, received, ordered, committed which are all different.
    Is anything missing from the business flow or need adding to it?
    Repeat this a couple of times, cleaning up and adding odd ball findings into it (hand written/verbal transactions, loss/gain reports, nonstandard suppliers)
    So now you have a mess of fields and examples you need to NORMALIZE and tablelize these examples.

    Biggest thing is don’t assume, confirm the sources, confirm the output, and confirm the connection and calculations. Work through your proposed tables and revise as necessary.

  13. #13
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Western_Neil View Post
    Like most things in a database, everything is backwards. Start by thinking what you want out of it, define that output into column headings, put some real thing's under those headings. Now are any those things calculated or combined, if so separate them into individual items.
    Now pull some input sources (shipping orders, purchase orders, etc) and create more column headings and data. Is all the data needed here? If not, where does it come from? Is all the data in each column the same type ie. Date can be shipped, received, ordered, committed which are all different.
    Is anything missing from the business flow or need adding to it?
    Repeat this a couple of times, cleaning up and adding odd ball findings into it (hand written/verbal transactions, loss/gain reports, nonstandard suppliers)
    So now you have a mess of fields and examples you need to NORMALIZE and tablelize these examples.

    Biggest thing is don’t assume, confirm the sources, confirm the output, and confirm the connection and calculations. Work through your proposed tables and revise as necessary.

    Wow, I've been learning about databases for 5 years and this right here is extremely helpful. Great tip, thanks!

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like your DB will differ from standard inventory DB as you have to follow the history of single item instead of history of article. I.e. the quantity of item you follow will be always 1! a possible structure for this:

    tblArticles: ArticleID, ArticleType, ArticleName, ... (The table used to group various items. The AtricleTyp is used to group various articles - e.g. hardware, software, service, etc. Yuo can also have several grouping fields, to allow structured grouping like material and immaterial articles.);
    tblItems: ItemID, ArticleID, SerialNumber, ...;
    tblTransactions: TransactionID, ItemID, TransactionDate, TransactionType;
    Probably some additional tables as registries for clients, suppliers, etc.;
    Probably some additional tables for financial info like ordering, invoicing, arrival, etc.;
    Probably a table where you link financial info with transactions.

    You need a form to register items;
    You need a subform in items form to register all transactions with active item. Transactions for given item can be of various types, like purchasing, reserving, selling, etc. The type of transaction determines, is the item added to your stock, or it is removed from it.;
    And of-course you need forms for managing all other tables mentioned above. And some reports.

    But those 2 forms above will be your main instrument.

    The current status of any item will be determined by latest entry for it in tblTransactions, which will be your most important data depository. You can determine the status of any item at any given date in past. You can find which quantity of certain item you have/had available in stock currently/at any given date. You can calculate, how many items of given article you did purchase/sell over given time period. You can find e.g. the certain article item which is available in stock currently, and has e.g. earliest/latest arrival/purchase date of all such items. Etc.

  15. #15
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by ArviLaanemets View Post
    It looks like your DB will differ from standard inventory DB as you have to follow the history of single item instead of history of article. I.e. the quantity of item you follow will be always 1! a possible structure for this:

    tblArticles: ArticleID, ArticleType, ArticleName, ... (The table used to group various items. The AtricleTyp is used to group various articles - e.g. hardware, software, service, etc. Yuo can also have several grouping fields, to allow structured grouping like material and immaterial articles.);
    tblItems: ItemID, ArticleID, SerialNumber, ...;
    tblTransactions: TransactionID, ItemID, TransactionDate, TransactionType;
    Probably some additional tables as registries for clients, suppliers, etc.;
    Probably some additional tables for financial info like ordering, invoicing, arrival, etc.;
    Probably a table where you link financial info with transactions.

    You need a form to register items;
    You need a subform in items form to register all transactions with active item. Transactions for given item can be of various types, like purchasing, reserving, selling, etc. The type of transaction determines, is the item added to your stock, or it is removed from it.;
    And of-course you need forms for managing all other tables mentioned above. And some reports.

    But those 2 forms above will be your main instrument.

    The current status of any item will be determined by latest entry for it in tblTransactions, which will be your most important data depository. You can determine the status of any item at any given date in past. You can find which quantity of certain item you have/had available in stock currently/at any given date. You can calculate, how many items of given article you did purchase/sell over given time period. You can find e.g. the certain article item which is available in stock currently, and has e.g. earliest/latest arrival/purchase date of all such items. Etc.

    Excellent, that's exactly what I was looking. Thanks a lot!

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-11-2015, 08:13 PM
  2. Replies: 1
    Last Post: 11-05-2014, 11:08 AM
  3. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  4. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM
  5. What is the best way to handle photos?
    By TundraMonkey in forum Database Design
    Replies: 2
    Last Post: 08-12-2009, 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