none
Update Top N rows in MS Access - where N is user defined RRS feed

  • Question

  • I have a database that assigns jobs/records to staff members.  They want to assign records N number at a time. e.g. 50 records to Sally, 150 to Linda, etc.

    Help.

    Friday, July 22, 2016 12:52 PM

Answers

  • Just a guess but more than likely, you will probably need a subquery to do this. Just a thought...
    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:40 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Friday, July 22, 2016 3:14 PM
  • As a simple example the following would update the status of the top n, entered as a parameter, rows in descending transaction date order to a value entered as a parameter, where the status is currently Null:

    PARAMETERS [Enter Number:] SHORT,
    [Enter status:] TEXT(50);
    UPDATE Transactions AS T1
    SET Status = [Enter status:]
    WHERE Status IS NULL AND
        (SELECT COUNT(*)+1
         FROM Transactions AS T2
         WHERE T2.TransactionDate > T1.TransactionDate
         AND Status IS NULL) <= [Enter number:];

    Transactions are analogous to your jobs, statuses to your employees and the date to whatever sort order you wish to use as the basis for the allocation.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Friday, July 22, 2016 9:12 PM
  • Hi Nina1990,

    According to your description, you could refer to below code:
    Sub UpdateTopN(num As Integer)
    
      strSQL = "Update( " & _
      "SELECT Top " & num & " TBL_Product.ProductName , TBL_Product.Quantity FROM TBL_Product ORDER BY ID) AS a " & _
      "Set a.Quantity=200"
      
      Debug.Print strSQL
      
      DoCmd.RunSQL strSQL
            
    End Sub

    In addition could you provide more information about your issue, for example screenshot, table structure etc., that will help us resolve it.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Monday, July 25, 2016 4:53 AM

All replies

  • Well, you need an order criteria to do this in one query or you need a VBA solution.

    So the question is: How are those 50 records connected to Sally? Or do you want to assign 50 random rows to her?

    Friday, July 22, 2016 1:36 PM
  • Just a guess but more than likely, you will probably need a subquery to do this. Just a thought...
    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:40 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Friday, July 22, 2016 3:14 PM
  • As a simple example the following would update the status of the top n, entered as a parameter, rows in descending transaction date order to a value entered as a parameter, where the status is currently Null:

    PARAMETERS [Enter Number:] SHORT,
    [Enter status:] TEXT(50);
    UPDATE Transactions AS T1
    SET Status = [Enter status:]
    WHERE Status IS NULL AND
        (SELECT COUNT(*)+1
         FROM Transactions AS T2
         WHERE T2.TransactionDate > T1.TransactionDate
         AND Status IS NULL) <= [Enter number:];

    Transactions are analogous to your jobs, statuses to your employees and the date to whatever sort order you wish to use as the basis for the allocation.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Friday, July 22, 2016 9:12 PM
  • Hi Nina1990,

    According to your description, you could refer to below code:
    Sub UpdateTopN(num As Integer)
    
      strSQL = "Update( " & _
      "SELECT Top " & num & " TBL_Product.ProductName , TBL_Product.Quantity FROM TBL_Product ORDER BY ID) AS a " & _
      "Set a.Quantity=200"
      
      Debug.Print strSQL
      
      DoCmd.RunSQL strSQL
            
    End Sub

    In addition could you provide more information about your issue, for example screenshot, table structure etc., that will help us resolve it.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    • Marked as answer by David_JunFeng Tuesday, August 2, 2016 2:41 PM
    Monday, July 25, 2016 4:53 AM