none
Trouble with an update query. RRS feed

  • Question

  • I may not be able to do what I'm attempting but figured I would ask the experts anyway.  I'm currently assigning work to be completed in Access via a "CID".  When a rep enters their particular CID in a text box on a form, it will return a query with all work that has been assigned to them.  What I'm attempting to do is select the records where the CID is null and then do an update.  The issue I'm running into is I want to break the records down and not select all. 

    So for example: There are 400 records and 4 reps.  I want to assign each rep 100 of the 400 records.  I know I must do this by rep but I would like to create an expression to do so.  I can get the select query to return me the top 100 records based on all criteria met but the second I change that exact select query to an update query, it reverts back to all records.  I'm fairly new to writing my own queries, so please assist if possible or share a simpler way to achieve what I'm attempting. 

    Thank you!


    • Edited by B_D_P Tuesday, July 5, 2016 11:19 PM
    Tuesday, July 5, 2016 11:18 PM

Answers

  • I do have a separate ID Field that is set to auto number.
    Provided your are happy with the top n arbitrarily being in ID order you can do it quite easily on that basis, e.g. in my RowNumbering demo to update the top 5 of the rows with a value <= 10 GBP:

    UPDATE Transactions
    SET TransactionAmount = TransactionAmount+1
    WHERE TransactionAmount <= 10
    AND DCOUNT("*","Transactions", "TransactionAmount <= 10 AND TransactionID <=" & [TransactionID]) <=5;


    Ken Sheridan, Stafford, England

    Wednesday, July 6, 2016 4:46 PM

All replies

  • While the Top predicate exists for Select queries, it does not for Update queries.

    But maybe you can try this ON A TEST COPY OF THE DB: create a select query with Top. Create an update query, and select both the table and this query, and inner join on the PK. Now your update should act only on those top records only.

    Why is CID allowed to be Null in the first place?


    -Tom. Microsoft Access MVP


    Wednesday, July 6, 2016 12:18 AM
  • Hi B_D_P,

    it is something unclear. you had mentioned that you want to complete this work via "CID" but you had mentioned that it is null. so how you are trying to do that? if it is null.

    you had mentioned that for example if there are 400 records then you want to assign 100 records to each.

    here I assume that CID means (Case ID) and Reps means Representatives please correct me if I understand it wrong.

    why don't you try to make a new column in this table named Rep ID and assigned that id to each 100 cases. Rep ID will also available in Representative Table and both have relationship. so that you can know that which case is assigned to which representative.

    and you can make Case ID to Auto Number.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 6, 2016 12:59 AM
    Moderator
  • As Tom has pointed out in an UPDATE query you cannot use the TOP predicate to return the subset of rows to be updated.  You can return the top n rows as an updatable recordset however by computing the sequential number for each row on the basis of the data.

    You'll find examples in RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file there are a number of queries whose result tables are made updatable by calling the VB DCount function rather than the more efficient methods of a join or subquery.  If this method is adapted to an UPDATE query as follows:

    UPDATE Transactions
    SET TransactionAmount = TransactionAmount +1
    WHERE TransactionAmount <= 10
    AND DCOUNT("*","Transactions","TransactionAmount <=10 And TransactionDate  <=  #"
    & Format(TransactionDate,"yyyy-mm-dd") & "#  AND (TransactionID <= "
    & TransactionID & "  OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)") <=5;

    in my demo the query will update the top 5 of those rows where the transaction amount is 10 GBP or less, incrementing the amount by 1 GBP in each case.

    This example should point you in the right direction.  First you need to identify a column or columns in your table on which it can be sequentially numbered.  Note that duplicates can be catered for by bringing an autonumber primary key (TransactionID in the above example) into play as the tie breaker.  Having then devised an expression calling the DCount function to sequentially number the rows where CID IS NULL you can use this in the WHERE clause to restrict the rows returned to the top n.

    Ken Sheridan, Stafford, England

    Wednesday, July 6, 2016 11:19 AM
  • CID is how we assign the work. So it's a blank field until I run an update query to populate certain work or enter manually.
    Wednesday, July 6, 2016 3:07 PM
  • The CID is contract ID and is what identifies the reps.
    Wednesday, July 6, 2016 3:08 PM
  • I do have a separate ID Field that is set to auto number.  I believe you may be on to something and will attempt your suggestion later this evening when I have exclusivity to the DB. Thank you! 
    Wednesday, July 6, 2016 3:14 PM
  • I do have a separate ID Field that is set to auto number.
    Provided your are happy with the top n arbitrarily being in ID order you can do it quite easily on that basis, e.g. in my RowNumbering demo to update the top 5 of the rows with a value <= 10 GBP:

    UPDATE Transactions
    SET TransactionAmount = TransactionAmount+1
    WHERE TransactionAmount <= 10
    AND DCOUNT("*","Transactions", "TransactionAmount <= 10 AND TransactionID <=" & [TransactionID]) <=5;


    Ken Sheridan, Stafford, England

    Wednesday, July 6, 2016 4:46 PM