Results 1 to 8 of 8
  1. #1
    otis is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Location
    Liberia
    Posts
    21

    Office Supplies to Employee (Inventory)

    Hi
    I need your help. I want to build a stock management inventory for the office. It is not a sale inventory, non-cash transactions. I want to have the total quantity of stock and employee can request stock via requisition slip; and as they request supply, it should be deducted from the total stock and shows the balances. Can someone help me get stable, cuz the database will be on a server
    Example 1 in MS Excel
    Stock Employee
    ITEM TOTAL QTY NAME DEPARTMENT Date QTY REQUESTING QTY BALANCE REMARK
    Sheet 20 Ream Mark Account Dept. March 1, 2015 2 Ream 18

    How to deduct the Stock from Stock Order and get the balance each time employee request stock

    Example 2
    Please help with the Design
    Office Stock
    StockID


    Stock Name
    Stock Description
    Stock Quantity
    Stock Balance

    RequisitionSlip (Employee)
    ID
    EmployeeName
    Department
    StockName
    Quantity Requesting
    Date of Request

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    The Total Qty is a calculation, not a stored value. also, don't confuse the display of data with the structure of the data.

    Total Qty and Qty Balance are the same thing - just at two different points in time.

    Qty Requesting needs to be split into 2 fields: first the number only (so math can be used) and the second is the Unit of Measure i.e. Ream which is just text

    your table should consist of fields:
    Item, Name, Date, QTY, Remark

    the Department is implied by the name - but if people change departments frequently they you could include it for long term tracking by department - if people stay in their department then it is there already assuming you have an employee table that includes department

    the Unit of Measure is implied by the item

    if you want to display Total Qty while the user is entering a requisition - that can be done a couple of ways. A simple sub form which displays an AggregateQuery is the most simple. Your Total QTY at any point in time is the running sum which is most easily viewed as an aggregate query.

  3. #3
    otis is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Location
    Liberia
    Posts
    21
    thanks a million veteran NTC, but i am a bit confuse here cuz i am a newbie to Access. My administrative manager buy a yearly supplies for the office, and employees of various departments order supplies for office use. Please see below the example and help me out sir, and trust me you will have a live long appreciation of a wonderful friend.
    Examples of how it works at the office
    In Stock Requisition Slip Statistic

    Goods Number of Goods in Stock Employee Name Department Qty-Request Date Balance Goods in Stock
    Hp Laser Catridge 20 PCs Bill Gates Accounts 1 Jan. 1, 2015 19 Pcs

  4. #4
    otis is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Location
    Liberia
    Posts
    21
    Update please
    thanks a million veteran NTC, but i am a bit confuse here cuz i am a newbie to Access. My administrative manager buy a yearly supplies for the office, and employees of various departments order supplies for office use. Please see below the example and help me out sir, and trust me you will have a live long appreciation of a wonderful friend.
    Examples of how it works at the office:


    Click image for larger version. 

Name:	New Picture (1).jpg 
Views:	25 
Size:	78.3 KB 
ID:	19924


  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    all my first post remains valid... so to help you understand in terms of what you just provided: the 'Requisition Slips' is your table - you will have many records/rows. The 'On Stocks' and the 'Statistics of Good' are not tables - those are calculations (Aggregate Queries) of your Requisition Slips table.

    of course requisitions are 'stock out' and there must be occasional 'stock in' - and so you do have to manipulate positive and negative values as part of the user interface so the calculations sum correctly but both Out and In are in the same QTYRequest column.

    this is a classic inventory database set up; again the structure of the table behind the scenes and what the display the user sees are 2 different things. I can totally recreate your display using the design I have explained. inventory can be confusing to a novice and it is simplest to just think of a list of requisitions that you occasionally sum for the On Stocks/Statistics.

  6. #6
    lauro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2015
    Posts
    2
    All, I am in the same boat. How can I produce and inventory data base.. and keep the balance active.

  7. #7
    otis is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Location
    Liberia
    Posts
    21

    help

    This is an office inventory, not a sales inventory. We supplies stocks (sheet, staple machine, letter head, etc) to employees by quantity request. We want to know the "Units In Stock' balance as employee request supplies. Example, if we have 10 reams of paper in stock and employee request 2, it should reduce to 8.
    And if we add new supply it should increase. Example if we have 8 ream of paper in stock and we add 10 it should increase to 18. Help!

    Look at this design and advise

    Click image for larger version. 

Name:	New Picture.jpg 
Views:	16 
Size:	32.6 KB 
ID:	19961

  8. #8
    lauro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2015
    Posts
    2
    how do i create the aggregate query to keep my inventory balance... current.

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

Similar Threads

  1. Office Supplies to Employee (Inventory)
    By otis in forum Database Design
    Replies: 21
    Last Post: 03-09-2015, 03:56 AM
  2. Replies: 4
    Last Post: 08-19-2014, 12:20 PM
  3. Office Supply Inventory
    By swhennen85 in forum Access
    Replies: 3
    Last Post: 02-01-2013, 08:38 PM
  4. Replies: 8
    Last Post: 06-06-2012, 12:28 PM
  5. Upgrading from Office XP to Office 2010
    By Mohamed in forum Access
    Replies: 1
    Last Post: 02-20-2012, 11:32 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