Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: Stock Control System.

    Hi, i'm new here and i'm trying to create a stock control system for an Entertainment store.
    I haven't really got very far at the moment. I've firgured out a few of the tables I need, i've got Stock Levels and Sales tables, which are both going to be related to 3 tables which will be of CDs, DVDs and Video-Games. I need to be able to automatically update stock levels going by the sales that have happened with a notification popping up when the stock level gets too low. I think I can do the last bit but I just really need some help getting started.
    Thanks in advance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a start:

    http://allenbrowne.com/AppInventory.html

    As recommended there, I wouldn't try to save the stock levels.
    Paul

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    Okay, i've tried using an Update query, but it's giving me a "Type Conversion Failure". I'll type the table fields and names in and copy and paste the SQL code in for my Update Query:

    tbl_Sales
    SalesID
    ProductID
    Quantity Sold

    tbl_Product
    ProductID
    Product Name
    Product Quantity

    And my Update query is:

    UPDATE tbl_Product INNER JOIN tbl_Sales ON tbl_Product.ProductID = tbl_Sales.ProductID SET tbl_Product.[Product Quantity] = ([Product Quantity]="tbl_Product.Product Quantity - tbl_Sales.Quantity Sold ON tbl_Sales.ProductID INNER JOIN tbl_Product.ProductID");


    Any ideas? Thanks.

  4. #4
    Join Date
    Apr 2008
    Posts
    3
    Okay, ignore the above post, now I have a different problem.
    I've managed to get it updating but it just looks up the Product ID in the Sales Table and changes the corresponding Product's Product Quantity to -1 and I have no idea why. Heres the SQL for the Update Query:

    UPDATE tbl_Product INNER JOIN tbl_Sales ON [tbl_Product].[ProductID]=[tbl_Sales].[ProductID] SET tbl_Product.[Product Quantity] = ([tbl_Product]![ProductID]=[tbl_Sales]![ProductID] AND ([tbl_Product]![Product Quantity]-[tbl_Sales]![Quantity Sold]));

    So basically, all Products in the Sales Table have been compared to the Product ID in the Product table and the values of Product Quantity has been changed to -1, instead of doing what I believe the query is supposed to do and subtracting the Quantity sold from Product Quantity.

    Cheers for any help.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is just another reason why you shouldn't store calculated values!
    George
    Home | Blog

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're basically asking for help on how to best drive your car off a cliff. Your SQL is not valid, and I don't think you need the sales table in there. Something like:

    UPDATE tbl_Product
    SET [Product Quantity] = [Product Quantity] - Forms!FormName.Quantity
    WHERE [ProductID] = Forms!FormName.ProductID

    That picks the quantity and product ID off the form. However, I repeat that I wouldn't do it. It is going to be a lot of work trying to maintain that value.
    Paul

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    For what it's worth I agree. Storing this value is going to just lead to complications down the track...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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