Results 1 to 6 of 6
  1. #1
    clarkproperties is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2020
    Posts
    7

    low stock queries

    I have a product table with unitsinstock and Reorder level i want to pull only the products that are equal to or less than reorder level.



    SELECT Products.[ProductID], Products.[Part Number], Products.[ProductName], Products.[Reorder Level], Products.[UnitsInStock], Products.[UnitPrice]FROM Products
    WHERE [Unitsinstock]<=[Reorder Level];

    What am i doing wrong im getting a mixture of both

    Thanks in advance
    Steve

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps
    Code:
    SELECT Products.ProductID
    , Products.ProductName
    , Products.UnitsInStock
    , Products.Reorderlevel
    FROM Products
    WHERE (((Products.UnitsInStock)<Products.Reorderlevel));

  3. #3
    clarkproperties is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2020
    Posts
    7
    SELECT Products.[ProductID], Products.[Part Number], Products.[ProductName], Products.[Reorder Level], Products.[UnitsInStock], Products.[UnitPrice]
    FROM Products


    WHERE (((Products.[UnitsInStock])<Products.[Reorder level]));

    Click image for larger version. 

Name:	2020-10-01.png 
Views:	22 
Size:	161.0 KB 
ID:	43084



    This is what i get

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Isn't unitsinstock a calculated field, which experience developers know that you should rarely use?

    http://www.allenbrowne.com/AppInventory.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    clarkproperties is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2020
    Posts
    7
    I think i found the problem the two fields were text instead of number on the products table
    thanks you guys for the help

    Steve

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  2. Stock DB
    By vugar in forum Access
    Replies: 5
    Last Post: 09-23-2015, 11:54 AM
  3. Stock balance
    By tgb in forum Access
    Replies: 1
    Last Post: 07-29-2015, 11:14 AM
  4. Cumulative stock by Id
    By Xur in forum Queries
    Replies: 5
    Last Post: 08-25-2013, 12:41 AM
  5. Stock-take
    By Cullen1109 in forum Access
    Replies: 12
    Last Post: 10-04-2010, 11:16 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