Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Inventory Locator (2000/SR1)

    I'm trying to do something that I thought would be very simple, and, of course, it's harder than I thought it was. I want to set up and inventory locator database. I have a table with products, locations and Inventory transactions. This will be a very basic system. What I have in the transaction table is the transaction ID, Date, Order #, product ID, location, transaction type (shipping or receiving) and quantity. What I want to do, if it is shipping, subtract the amount from the total that is in the location and if it is receiving, add it to the total. I would rather not have seperate fields for in and out, rather make it so that the system checks as to the type of transaction and then sets the quantity accordingly, plus or minus. I think I can do that with beforeupdate on the quantity field, although I haven't tried it yet. Does that sound llike it will work?

    The other question I have is how do I set up the math, so that it will compute the quantity in each location and am I asking for problems if I put 2 different products in the same location? Thanks in advance for any help.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Inventory Locator (2000/SR1)

    You could leave the quantity as a positive and use the transaction type in queries to determine the sign.
    You could put the sign on the quantity in the BeforeUpdate of the form.

    <<The other question I have is how do I set up the math, so that it will compute the quantity in each location and am I asking for problems if I put 2 different products in the same location?>>

    What is the problem with putting 2 different products in the same location?
    Pat

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Inventory Locator (2000/SR1)

    Thank you for your help.
    As far as putting 2 different products in the same location. I'm not sure if there would be a problem, I'm just wondering if anyone else has done this and has it worked out OK.
    Thanks again.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Locator (2000/SR1)

    I have a StockIn/StockOut application that may be similar, but I do not count the quantity in a particular location. I count the quantity per product (because they really need to know how much of a product is on-hand) and list it's location. For each Location, I keep an Available flag that gets set to No (when the location is used) and Yes (when the location is empty), so that they know which locations are available to stock incoming product.

    I think the constructs need to be specific to the process in the warehouse. If they stock more than one type of product in a location, then the design should model that. It may be easier if one product = one location, but is that a process they can work with?

Posting Permissions

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