Results 1 to 12 of 12
  1. #1
    ducttapeboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    5

    Product Tracking Database

    I'm fairly proficient with Access, but I can't seem to wrap my head around the best way to set this one up. Basically, I want to track milk milk sales for a small store. Main features would be as follows:



    Chart current inventory
    Track sales and losses (both recent and long term) by date
    Suggest Order Amounts (Use current sales and inventory levels to determine minimum to order)

    I've been looking at different inventory management databases, but none seem to quite do what I want. I don't need to track customers and all milk comes from a single supplier. I'm not trying to track costs, invoices or anything to do with billing. All I want is the inventory sales, orders, and losses.

    Would it be best to have three seperate tables--a Sales Table, Inventory Table and Losses Table? Each table would be basically:

    (PK) ID
    (FK) ProductCode (Link a Products table listing the different types of milk.)
    Date
    Quantity

    Would it be better to have a single table (instead of the three) that has a field to specify if the record is a sale, inventory count, or loss.

    I just can't seem to wrap my head around how to set this up and generally that means that I'm looking at it wrong.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I think the inventory count will have to be a separate table because will have to calculate the difference between records - review http://allenbrowne.com/subquery-01.html#AnotherRecord

    How are losses determined?

    Review: http://allenbrowne.com/AppInventory.html You could ignore the Invoice aspect.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ducttapeboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    5
    Basically, losses would be entered manually from any products that go out of date. To give you an overview of the process, milk is ordered Wednesday for a Thursday delivery and Friday for a Monday delivery. Milk is inventoried (to get the on hand total) after each delivery and before each order. Total sold is calculated by subtracting Wednesday's on-hand amount from Monday's total.

    Sadly, this is very old fashioned and they don't track sales at the register with bar code scanners.

    I checked out the two links and I like the idea of a subquery to view sales from previous weeks. Thank you for that!

    I also like the idea of a stocktake. That seems like that would be a good trick to have.

    So do you think I should set up something similar to the Acquisition and Acquisition Detail tables for the Inventory, Orders, and Losses tables? Sales could be calculated, but the other three would probably be entered manually.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Possibly. Need some method to account for product in/out. Conventional model is Purchases (Acquisitions) and Sales (Invoices) to provide tracking and accountability. And then the 'stocktake' to deal with loss/overage - inventory count does not agree with inventory calculated from Purchases and Sales.

    If you just want to record manual counts, that is entirely different scenario.

    Don't need bar code scanners to have real-time data entry/reporting. Sure, scanners can speed up the data entry but not mandatory.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ducttapeboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    5
    First of all, thank you very much June7. I really appreciate the quick and helpful responses. You're awesome!

    What do you think of this setup? I think it's a modified version of the you linked earlier. Instead of an "Acquisition" table I have a "Transaction" table and a "Transaction Detail" table (sorry, I couldn't think of a more suitable name). Basically, the Transaction Table stores dates and specifies if the count is a daily inventory, order or loss count. The detail table then ties in the product code and the count associated with each product.

    Here's the design of the tables:

    ProductTbl
    (PK) ProductCode
    ProductDesc
    ProductDefaultStock (Number specifying minimum amount of each product I want to keep in stock at all times)

    TransactionTbl
    (PK) TransID
    (FK) TransType
    TransDate

    TransDetailTbl
    (PK) TransDetailID
    (FK) TransID
    (FK) ProductCode
    TransDetailCount

    TransTypeTbl
    TransTypeID
    TransTypeDesc (Different types would be Inventory, Order, Losses)

    Bearing in mind that "Transaction" should really be something more descriptive like "Count," does this make sense or am I making it difficult?

    Thanks again for your help!

  6. #6
    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

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    I'm somewhat confused about your TransType's.

    In such transaction table, you usually register all movements (in and out). The easies way will be to enter incoming quantities as positive numbers, and outgoing as negative. And you must have ProductCode registered for every those transaction too!

    In case of a store, incoming are purchases or products from some vendor (yourself, when you sell the milk from your own farm, some farmers, or some other soured(s). Is this the type Order in your case?

    Outgoig are:
    1. Your sales of products for day. Is this the transaction type "Inventory"? Or do you mean with inventory the remaining quantity at end of day - in this case the transactions table is no place for this!;
    2. Your losses (i.e. the quantity of products you had to scrap);
    3. You can consider adding an additional type to correct remaining stock at evening inventory count (this type can be either positive or negative).

    You can register the amount in stock for every product at evening in separate table (with date registered) - so you don't need to calculate stock saldo over all history. You can write a code, which adds correction rows into transaction table after you have the inventory made and the amount in stock for all products registered.

  8. #8
    ducttapeboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    5
    Yes, I apologize for the poor naming of the tables. Basically, my idea is this: the Transaction Tables contain product counts. (In fact, I will probably change the name from Transaction to "Count.") The Transaction Type would specify what type of count is being stored: i.e. when counting the on hand inventory, it would be labeled as "Inventory"; out of date product would be ordered as "Losses"; and orders would be labeled as "Orders."

    Basically, I threw that in because I was planning on using a lookup wizard to display more user friendly descriptions of the TransTypeID field in the TransactionTbl. It's probably not needed because I imagine that the front end would just have different forms for each count and so the "TransTypeID" would be determined by the form used.

    Does that make sense? I seem to be having a hard time explaining it. (Which is probably not a good sign...)

    Thanks to everyone for the help! Orange, I'll check out those videos tonight.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Order count should be verifying the quantity received per some order document. Surely you have order documents? This would be tblAcq and tblAcqDetails tables in Allen's model.

    And for sales surely there are vouchers of some sort even if just cash register tape. This data would be entered into tblInvoice and tblInvoiceDetails.

    You say you don't have POS technology so I can't imagine business is operating without paper.

    In your case, might be able to combine into one transactions table. Depends how much repetitive data there is. If each order and each sale can have multiple products, really should have parent/child dependency.

    You say milk is inventoried after every delivery and before every sale - the entire warehouse (giant refrigerator?)?? Sounds very inefficient.

    In conventional system, inventory count is used as a check against the calculated net of orders/sales. This is where the loss/overage correction is derived. Inventory count is not supposed to be occurring multiple times in a day. Inventory count is not a transaction of product movement. I already stated this should be its own table. The loss/overage for any given date would be determined by calculation. Formula: Inventory count on given date - (Sum(Acq thru given date) - Sum(Invoice thru given date)). An accounting system will perform 'period closeout' calculations to determine these corrections then post 'adjustment journal entry' transactions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ducttapeboy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    5
    Inventories are taken before each order and after each delivery. It works out four times a week. It's not terribly efficient, but it's a small store so it doesn't take long. On Mondays, we get a delivery, so I take the pre-delivery total + delivery - out of date products for a total on hand. Wednesday, I take an inventory before doing an order. Sales from Monday to Wednesday are calculated using Monday's Total - Wednesday's Total - out of date products. Thursday we get a delivery, so I do another inventory totaling pre-delivery milk + delivery - losses. I can track sales from Wednesday to Thursday by subtracting Thursday's pre-delivery total from Wednesday's total. I have to place Monday's order on Friday, so I do another inventory count so I know what I need to order.

    Basically, the current system calculates what sold from the inventory on hand subtracted from the previous count's total minus any losses. So if on Monday, we had 30 gallons of whole milk on hand and Wednesday we have 15, I would assume we sold 15. If 2 of the 15 on Wednesday are out of date, then we would have sold 13. It's not a perfect system, but unfortunately, we don't have a system that can show how many of each product is sold each day.

    Basically, I want a database to track losses and sales both on a week-to-week basis and from year-to-year.

  11. #11
    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
    Thanks to everyone for the help! Orange, I'll check out those videos tonight.
    Watch some of the videos, then do one or two of these:

    [CODE]
    For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials.
    Class info system
    Catering Business
    Widgets
    [
    /CODE]

    Then, using your own description of your business, apply the procedure you used when you worked through the tutorial. Then post your attempt and any questions you may have.

    Good luck.
    Last edited by orange; 12-01-2017 at 05:05 PM. Reason: spelling

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You describe collecting data like:
    ID CountDate InventoryCount ReceivedCount SpoiledCount
    1 11/27/2017 100 20 4
    2 11/29/2017 110 0
    7
    3 11/30/2017 108 30 5
    4 12/1/2017 110 0
    8

    InventoryCount is count of all units before received units added and before spoiled are removed - every unit sitting in the frig. Record 1 shows a net of 116 units to be accounted for in the next count. Record 2 InventoryCount does not equal the net of record 1 by 6 units. You assume this difference is due to sales? There are no sales documents to support this calculated value? This is very poor business accounting practice. Do you have documentation to support the product received data? Why can't you implement a procedure to document sales?

    This is a store so I presume other products than milk are sold. How is inventory managed for other products? Why this focus on milk?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  2. Replies: 2
    Last Post: 08-14-2014, 11:49 AM
  3. Product Database
    By W-technologies in forum Database Design
    Replies: 2
    Last Post: 03-14-2013, 08:31 PM
  4. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  5. Product Order/Pricing Database
    By atg in forum Database Design
    Replies: 3
    Last Post: 06-28-2012, 05:45 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