Results 1 to 6 of 6
  1. #1
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88

    re-order level


    hello i have a query where im trying to create reorder level the most basic one if possible

    i have a an item table with field name QuantityinHand i just also added another field and named it reorderlevel

    and i have also created another new table called "T Reorder" with 2 fields an Auto ID and a "ReorderYesno" Field which has 2 rows, yes and no

    i need to write a code which looks similar to the indicated below

    Reorder: IIf([QuantityinHand]<[reorderLevel] then ([reorder].[ReorderYesno]="yes",else [ReorderYesno]="no"

    i will create a report out of the T Reorder, and set a query as a sub report below, and set master child field to yes, and all the ones which have yes will show up, and we got ourselves a reorder report based on whatever is less then the reorderlevel

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    A. Quantity on hand should not be a value in a table, it should be a calculation based on receipts and disbursements of an item.
    B. On your product table (at the very least) you would need a reorder level FOR EACH ITEM where applicable.
    C. If the reorder level is present you can then distinguish between the 'yes' and 'no' reorders
    D. If the quantity on hand (based on the calculation) is below the reorder level, then you can have it show on reports that prompt you to reorder items.

  3. #3
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    is there a way of providing u the database however it is split into a back-end and a front-end db so u can see what is going on

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    yes you can create a copy of your database and take out any private information, then put enough junk/sample data in to reproduce your problem. Then zip it up and upload it here via the GO ADVANCED button.

    Only put into the database the necessary items (tables, queries, reports) to display the problem.

  5. #5
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    i have the following in a query

    Code:
    Reorder: IIf([QuantityinHand]<[reorderLevel],"Reorder","")
    it auto populates the word "reorder" in the Reorder Field, whenever a QuantitynHand is less then the minimum required level which is written reorderlevel field,

    i just need to cancel out the blanks in in the reorder field, and i tryed putting
    Code:
    is not null
    in the criteria of reorder, but it didn't work

    any suggestion would be greatly appreciated!

  6. #6
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    well all i had to do was write
    Code:
    like "reorder"
    " in the criteria and it cleared out all the banks k thx for the information which u have provided me with

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

Similar Threads

  1. Linking Tables at the Table Level Vs. the Query Level
    By Tammy in forum Database Design
    Replies: 3
    Last Post: 12-03-2014, 01:34 PM
  2. Access level?
    By lithium in forum Security
    Replies: 2
    Last Post: 11-02-2014, 03:35 PM
  3. Allow more than order in an ORDER form.
    By kiko in forum Access
    Replies: 37
    Last Post: 04-19-2013, 05:30 AM
  4. Bi-level grouping
    By workuser in forum Reports
    Replies: 2
    Last Post: 03-18-2013, 10:46 AM
  5. How to order columns in ascending order?
    By darkingthereturn in forum Access
    Replies: 5
    Last Post: 06-18-2012, 05:24 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