Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inventory (Access 97)

    Hello to all of you.

    I am working with an inventory form that is part of my database. I also have a Purchase Order form that is the subform for the Inventory form. The Purchase Order form gets populated with the Purchase Order table except for the calculated. The calculated field gives me the total of all purhcase units that have been bought per Part Number (this part number is what defines each book that is being baught to get sold later).

    Now that I have this I also want to be able to deduct all the units per Part Number that have been sold to our customers. I have a Sales Order form with a with a subform named SalesItems. In these Sales Items form all the part number and qty that are sold are entered and saved into the Sales Items table.

    My question is how can I get my database to deduct all units per part number that are sold to be deducted from inventory.

    Thanks in advance for your help.

    Lucy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: inventory (Access 97)

    In order to make this sort of thing happen automatically, you need to use VBA to either execute some queries to modify the values in your inventory tables, or you need to write a more complex set of VBA instructions that use DAO (Access 97 doesn't really support ADO) to alter the table values in code.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: inventory (Access 97)

    Lucy,
    do I understand your question right as 'I want to know how much I still have in stock, meaning: total purchased - total sold'?

    In that case, I would calculate this using total queries (see attachment):
    - qsumSales_Product: how many of this product have been sold
    - qsumPurchases_Product: how many of this product have been purchased
    - qryStock_Product: substracts sales from purchases per product => OK!
    I used outer joins in the latter to show all products, also those of which none haven't been purchased or sold yet.

    FYI... using this scenario, as through time items can get lost/stolen/damaged, I guess you do an inventory now and then... So I would also
    (i) add a tblInventory containing the inventory's results (date, productID, number) so you can enter (read: reset) the monitored number of a given product in stock at a given time;
    (ii) add another query selecting the last (read: most recent) number;
    (iii) edit qsumPurchases_Product and qsumSales_Product by limiting them to only those sales/purchases done *after* the last inventory.

    Then, you can add the 'stock' value for the product in question to your Purchase order form, e.g. using a lookup function/...

    Fyi... if you want to monitor which products are almost sold out, you can create another query sorting qryStock_Product (ascending) on Stock (eventually combined with how many of this product have been sold for the last month(s), giving an indication about which products are the most popular ones and should probably be purchased first), etc... and make this list available on request through a command button in your Purchase form.

    Hope this was somehow an answer to your question... and that I didn't reinvent the warm water too much... as I guess there must be sample databases providing you ready made solutions for this (NorthWind?)

    Take care,
    Hasse

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inventory (Access 97)

    Hasse

    Nice example of not storing quantity on hand

    Thanks, John

Posting Permissions

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