Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update inventory table (Access 2000)

    I've got an Inventory table that I need to update when I place an order against it from my Invoice form. What's the best way to subtract the order so that I can have a running inventory?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Update inventory table (Access 2000)

    Before answering the question, you are going to need to give us a little more details on what you are doing.

    What is the primary key on the inventory table?
    You have an invoice form. Does it populate an invoice table?
    What is the common field for the inventory item between the inventory and invoice table?

    Other items to consider?
    How does your system know when ordering or creating an invoice that there is enough inventory to support the request?
    What happens if your inventory goes negative based on an invoice?
    Do you decrement the inventory table upon creating an invoice or when the invoice is paid or item(s) are shipped or received by the purchaser?
    How do you handle returns? etc.


    Before answering your question, we would need more information as there are various way to do this. Would you need to keep track of items that are paid, shipped, and not received (i.e. in transit). I would keep several tables all related by an inventory ID. Then you could have one table for inventory. One table for invoices. One table for inventory shipped with a flag for received. One table for inventory returned. The running some of inventory available would be total inventory less shipped plus returned which could be done via a query.

    Well, this may be a start.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update inventory table (Access 2000)

    If possible, you should consider NOT maintaining a running inventory balance. Use queries to get the sum of inventory received and subtract from that the sum of inventory dispensed. This is MUCH safer, as it is very easy to not properly update that running total for each and every different type of inventory activity.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update inventory table (Access 2000)

    The inventory table just has Product ID (PK), Product Name, Product size
    Currently, I was planning on an Invoice table to track the orders
    The link between the two would be the Product ID

    Due to the business (seed corn) it is possible to have negative inventory, so the system can allow a negative inventory. Part of the inventory management will have to handle product transfers from dealer to dealer.
    I would like to decrement inventory at time of invoice so that the dealer knows his on-hand position
    Returns are not an issue

    I will need to track product delivery though. The customer will order seed in one size and very well will receive a different size at delivery time.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Update inventory table (Access 2000)

    So,

    You have a material or materials where the material (corn in this case) is stored. I assume that the inventory of the material will increase and decrease as the supply and demand changes throughout the year. Thus you have a one to many relationship between the main inventory and transfers out (many different buyers) and a one to many relationship between the main inventory and transfers in (many different suppliers). If this is true, you would want to store suppliers and dealers names as well so you can associate them with the product. This will also help for detailed reports in the end for those that had to most quantity or perhaps, were most profitable.

    You need to determine what your primary key will be for the main inventory table. Your Product ID may work. Product name and size are also good secondary fields however you might want to be more specific with some fields so you can better create reports. Fields to hold product, type, size, and other fields may help. (i.e., Corn, Yellow, Feed, .25inch??, etc..) You could then easily query the data by grouping / filtering on these fields.

    Your comment of transfers from dealer to dealer can get confusing. If you were to ship a quantity of 10 to dealer A and he ships 5 to dealer B, the total shipped is still 10, however, how you you get the data for other shipments from the other dealers.

    So, as I see it, you would need the following as a starting point (I would suggest you lay this out on a white board or paper to make sure it makes sense. If your foundation isn't any good, nothing will work in the end.)

    - Material Table that stores all of the ID's and other key information for material (This is not the inventory table)
    - Vendor Table (A vendor could be a supplier or dealer) Include key vendor information here.
    - Inventory Table to store additions and deletions to your inventory.

    Table Relationships
    - Your inventory can be made up of one or many materials. (One to many link between inventory and material)
    --- Is it possible for an inventory of material (Yellow Corn) be made from various yellow corn inventories? If so, watch your primary key(s).
    - Your Inventory can be supplied by or delivered by one or many vendors. (Another one to many link or you can just use the vendor table as a lookup to identify who added or depleted material.

    So, If you set up a form that shows the inventory and have an add material and subtract material button based on the inventory and vendor selected, you have it made.

    Easy, ins't it.... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Sorry if I have confused you. Let me know how it is going. You might want to search online for other similar databases where it may be easier to get one off the shelf then designing one.

    HTH.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update inventory table (Access 2000)

    Thanks. That helps a lot. However, my original question still has me perplexed. How do I subtract from my inventory table once an order is placed? Would I use an update query?

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Update inventory table (Access 2000)

    I am with Mark on this one. You wouldn't change the inventory table at all. Your inventory table would be used to create the items in the inventory. You would link this table to another one as a one to many where the second table would track all additions or deletions to the inventory. You would then use a query to calculate the total additions and subtractions and the net remaining of the inventory.

    Setting up the inventory with all changes in one table is like using an excel spreadsheet which defeats the purpose of a reational database.

    Again, if you code the additions / subtractions table intelligently with fields such as year, material type, etc., you should be able to create a multitude of various reports that would be helpful.

    HTH.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update inventory table (Access 2000)

    Read attachement it may help with the understanding
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •