Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2017
    Location
    Wichita KS
    Posts
    4

    Smile Complex Inventory Project

    Hi Everyone, I'm new to Access... I understand most concepts of how databases work, but have little experience/knowledge of specific functions and efficient layout/designs. I have a project I have been writing notes and brainstorming, and I need help on, not only layout, but execution of a liquor inventory system for a bar that my father and I are running. Here are the details:



    The idea is simple enough. I need to input total units on hand AND report units sold each day, and to be compared to previous days to determine differences and anomalies... Ex: Sold 3 bottles, but 4 are missing, so we are 1 bottle short. Thats the first part...

    Next, I need a convenient form for input. In this case, I need to input individual bottle count (in this scenario, a cooler door), but I also need to count back up cases of a certain amount of bottles. Ex: 12 bottles in cooler door, 1 twelve pack and 1 case of 24... totaling 48 total on hand..... I need to be able to add or edit case amount/packages (in case i get a 20 pack or rare beers in 4 packs, etc.)

    Food is of similar layout to beer as we portion into countable units.... Same rules apply...

    Third..... The more complex part...
    I still need to calculate difference of units on hand vs sold, BUT this is liquor poured and measured by weight...
    So our average shot weight is 40 grams per 1.5 oz shot..... those need to be able to be changed/adjusted, but not a priority.
    I also have to be able to adjust average empty and full weight of incoming and depleted bottles... The calculation goes as follows: Ex:
    bottle of liqour weighs 600 grams. Empty weight for that particular liqour is 400 grams.... difference is 200 g and at 40g a shot, I have 5 shots or units
    but I have a back up bottle full weight 1200 g full, 400 empty, diff. 800 g so 20 shots/units...
    Once this conversion is complete, units can be calculated same as beer/food...


    Next, of course, is sales category... certain liqours are catergorized as calls, some as wells... as these are the categories for sales report input..... so once each bottle units has be determined, I need to sum each category to determine long/short calculations....

    I also need to add new products of each category, or deactivate liquor we are discontinuing... not delete, as we may bring certain product back.... I there is a convient way to add in cost calculations that would be great too...



    I need to input (through form/query, im assuming) which days to calculate and display report by date and day to show what days we were short/long. I need to compile this for at least 8 day segments, so they can overlap for comparison, I'd prefer month long segments, but I would like to see a year long database system if possible...

    And the Icing on the Cake? I need the forms/layouts to be efficient/intuitive as possible, as the employees I hire aren't always the most proficient at using computers...



    Thanks in Advance for any and all advice and lessons, no matter how big or small...And if I'm in the wrong place, please let me know and direct me to the better place to be... Thank you


    P.S. I know most of what I need calculated can be done in Excel, but I can't seem to figure out efficient input of all of the liquor weights....

    Edit- on liquor weights... I also need to determine what size of bottle I'm calculating... I have 1 Litre and 750mL bottles and need to calculate in case I have both sizes of One Liquor on hand

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are several references/articles on Inventory/Stock Management that you can find via Google/Bing. I'm sure if you review a few you'll find some options on approach and design. You may also find that inventory is not necessarily a trivial subject/application.

    Here is a link to a youtube video re Stock management -- it's about 26 minutes long and deals with many of the concepts involved.

    For more info on database concepts and planning see this link.

    You should be aware that Excel and Access are very different animals -built on different object models, serving different purposes.

    Good luck with your project.

  3. #3
    Join Date
    Dec 2017
    Location
    Wichita KS
    Posts
    4
    Thanks for the reply... I've reviewed the links, and they will serve me well in my construction, but I have a question, more towards specific functions.... Since I'm trying to store data on a day to day basis for calculation and record viewing, I'm stuck with a organizational conundrum.... I need to have rows to serve as days input, similar to normal data entry, but with a way to calculate conversions, but i have to also update/add inventory that would serve as fields...

    My question is: is there functions that creates fields(Columns) in one table from rows in another table.... Im not sure if Access is capable of that layout, but I cant seem to find an answer anywhere...

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My first reaction is that you may be confusing how to do something with what exactly that something is.
    In typical inventory management, you receive goods and you sell goods. This is often done using a transaction table. Incoming goods are positive transactions and outgoing/sold goods are negative transactions. You can calculate stock at any time (in theory) using a query where
    Current Stock = Total Incoming - Total Outgoing

    but there may be theft or waste or loss. So every now and then you must do a physical count.

    See this article by Allen Browne for details of Quantity On Hand.

  5. #5
    Join Date
    Dec 2017
    Location
    Wichita KS
    Posts
    4
    Lol, I see the confusion in my explanation.... That waste/loss is what this database is for.... I need to count by hand as you stated, but input the results.... it is for raw, on hand, count calculation/weight conversion to units and then is compared to a register sales report.... while reorder/stock management is a feature I will add, my issue is the calculations and data storage to determine theft, loss of product, or negligent give away of product... Sorry if my post seems convoluted....

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Let me explain it step-by step.

    You buy some goods/articles and then sell them.

    The first table your database need is the list of all articles you buy or sell - you can't buy or sell anything what you haven't registered before
    tblArticles: ArticleID, ArticleName, ...

    Then you need an inventory table, where you register periodically, how much of every article you have in stock at given date. NB! This is not for everyday inventory! You can make it p.e. at end of every year, and register real quantity of every article at inventory date early midnight (i.e. InventoryDate 00:00:00). And whenever you register a new article through the year, you may enter it into inventory table too - with quantity equal to 0. Or you can assume, that when article is missing from Inventory table at given inventory date, then it's inventory quantity is 0.
    tblArtInventory: ArtInvID, ArtID, InvDate, InvQty

    And then you need a table where you register all transactions, like Orange wrote. Incoming articles positive and outgoing articles negative quantities.
    tblTaransactions: TransactID, TransactDate, TransactType, ArtD, ArtQty, TransactPrice
    Mark the field TransactType. You can have defined the transaction types as "buying" and "selling" too, or you leave it empty for ordinary transactions, but you define additional types like as "missing" or "surplus" or "scrapped" etc.

    As next you create a saved query
    qryCurrentData:
    Code:
    SELECT inv.ArtID, inv.InvQty FROM tblArtInventory AS inv WHERE inv.InvDate = (SELECT MAX(inv0.invDate) FROM tblArtInventory AS inv0) UNION SELECT tran.artID, tran.ArtQty FROM tblTransactions AS tran WHERE tran.TransactDate > (SELECT MAX(inv0.invDate) FROM tblArtInventory AS inv0)
    And another one
    qryStock:
    Code:
    SELECT  stock.ArtID, SUM(stock.artQty) AS ArtBalance FROM qryCurrentData AS stock
    Now you can start the work! At evening you run the query qryStock, and compare it with your daily inventory. When there is some difference, you register it with appropriate TransactType (positive quantity when surplus, negative when missing). When after that you run qryStock again, it must match with your daily inventory now.

    Separate queries can give you non-zero totals of "missing" and "surplus" for every article for given period (day/week/minth/year).

    Also you can account, how much of goods you had to scrap because p.e. going over realization term etc.

  7. #7
    Join Date
    Dec 2017
    Location
    Wichita KS
    Posts
    4
    Ok, thats a good basis for me to start on.... I still have to learn alot of the new layouts and functions, since the last time I had to do any DB work was in Access 2003... But this gives me a much clearer view of what I'm looking at/for to attack this problem... Thanks for all of the replies!

  8. #8
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    don't forget the units table with conversion factors: you buy your goods in bottles, but most of the time sell it by glass.

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: 8
    Last Post: 07-16-2014, 12:51 PM
  4. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  5. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM

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