none
Update Syntax RRS feed

  • Question

  • Good Day - I'm working on a form to assign work in Access. I have all of the other pieces set up besides this last part.  So the first part of the form the supervisor would select the type of work they want to assign from a drop down.  I linked the form to my query to pull the work (Priority) that does not have any attempted calls made.  That works fine.  Next the sup selects who they want to assign the work to (CID) from a drop down.  The last part is where it gets a little tricky.  The last option on the form for the sup to select is the number of records they want assigned to the CID they have selected based on the priority.  What I would like the update query to do is assign the CID to the records that were pulled by Priority based off what ever number the sup enters into the box on the form.  So if they have selected TOU as the priority and CID 01234 as the rep they want to assign work to and 20 in the quantity box, I want the update to take 20 records that fit the criteria and assign to that rep.  Any assistance you guys can provide would be greatly appreciated. 
    Monday, July 25, 2016 9:05 PM

Answers

  • Surely this is an INSERT operation, not an UPDATE operation?   Taking a simple model as an example, with tables Employees and Tasks, the relationship type between them can modelled by a third table, let's call it Assignments, e.g.

    Assignmenents
    ….TaskID
    ….EmployeeID

    An unassigned task is identified by the absence of a referencing row in Assignments.  Let's assume the Tasks table has a column Category.  So to assign 20 tasks of category 'Urgent' say to the employee with the EmployeeID value 42, the code would be like this:

    Dim rst As DAO.Recordset
    Dim strSQLUnassignedTasks As String
    Dim strSQLTasksToBeAssigned As String
    Dim n As Integer

    strSQLUnassignedTasks ="SELECT Tasks.TaskID FROM Tasks LEFT JOIN Assignmenents " & _
        "ON Tasks.TaskID = Assignmenents.TaskID WHERE Category = ""Urgent"" " & _
        "AND Assignmenents.TaskID IS NULL"

    Set rst = CurrentDb.OpenRecordset (strSQLUnassignedTasks)

    With rst
        .MoveFirst
        Do While Not .EOF
             strSQLTasksToBeAssigned "INSERT INTO Assignments(TaskID,EmployeeID) " & _
                  "VALUES(" & .Fields("TaskID") & ",42)"
             CurrentDb.Execute strSQLTasksToBeAssigned, dbFailOnError
             n= n+1
             If n = 20 Then
                 Exit Do
             End If
             .MoveNext
        Loop
    End With

    The values for Category,  EmployeeID and the maximum value of the variable n would of course be obtained from the user in reality, and the SQL statements built accordingly.  

    Hopefully this will give you an idea of the basic methodology for this sort of thing and point you in the right direction.

    Ken Sheridan, Stafford, England


    Tuesday, July 26, 2016 6:00 PM

All replies

  • Hi B_D_P,

    From your description we are only know that you had mentioned some fields of the tables.

    but it is better if we know the table designs and the query you are using to fetch the above data.

    because we don't know which fields belong to which tables. so how can we suggest the query for that.

    you had mentioned ,"The last option on the form for the sup to select is the number of records they want assigned to the CID they have selected based on the priority" How you got these records.

    if you want to assign these records to cid then you can make a new field in the table that contain the records and update the cid in that table for that records.

    if this not solves your issue please try to share your tables and query designs so that we can try to provide you further suggestions.

    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.

    Tuesday, July 26, 2016 3:16 AM
    Moderator
  • Thank you for your response. All of the fields belong to a central table named exceptions. CID and Priority are fields in that table. Right now the Update query has those two fields included and an additional field called 1st attempt where the criteria is null. The query pulls the work based on Priority first and then I want the Update to enter the CID selected from the form into the CID field based on the number entered into the quantity field on the form.
    Tuesday, July 26, 2016 3:06 PM
  • Are you trying to do this without VBA?

    Best regards, George

    Tuesday, July 26, 2016 4:51 PM
  • Hi. If you want the number of records to update to be dynamically selected by the user/supervisor, then I agree with George. You will need to use VBA to do it. I don't think the SELECT TOP keyword can be modified dynamically other than through VBA. Just my 2 cents...
    Tuesday, July 26, 2016 5:08 PM
  • Surely this is an INSERT operation, not an UPDATE operation?   Taking a simple model as an example, with tables Employees and Tasks, the relationship type between them can modelled by a third table, let's call it Assignments, e.g.

    Assignmenents
    ….TaskID
    ….EmployeeID

    An unassigned task is identified by the absence of a referencing row in Assignments.  Let's assume the Tasks table has a column Category.  So to assign 20 tasks of category 'Urgent' say to the employee with the EmployeeID value 42, the code would be like this:

    Dim rst As DAO.Recordset
    Dim strSQLUnassignedTasks As String
    Dim strSQLTasksToBeAssigned As String
    Dim n As Integer

    strSQLUnassignedTasks ="SELECT Tasks.TaskID FROM Tasks LEFT JOIN Assignmenents " & _
        "ON Tasks.TaskID = Assignmenents.TaskID WHERE Category = ""Urgent"" " & _
        "AND Assignmenents.TaskID IS NULL"

    Set rst = CurrentDb.OpenRecordset (strSQLUnassignedTasks)

    With rst
        .MoveFirst
        Do While Not .EOF
             strSQLTasksToBeAssigned "INSERT INTO Assignments(TaskID,EmployeeID) " & _
                  "VALUES(" & .Fields("TaskID") & ",42)"
             CurrentDb.Execute strSQLTasksToBeAssigned, dbFailOnError
             n= n+1
             If n = 20 Then
                 Exit Do
             End If
             .MoveNext
        Loop
    End With

    The values for Category,  EmployeeID and the maximum value of the variable n would of course be obtained from the user in reality, and the SQL statements built accordingly.  

    Hopefully this will give you an idea of the basic methodology for this sort of thing and point you in the right direction.

    Ken Sheridan, Stafford, England


    Tuesday, July 26, 2016 6:00 PM
  • I don't care what method is used to accomplish the goal.
    Tuesday, July 26, 2016 7:39 PM
  • You are correct.  The Update function does not recognize Select Top...I tried it!  lol
    Tuesday, July 26, 2016 7:40 PM
  • Alright Ken...While I'm sure that answer would work, it's way above my pay grade. Is there a simpler way to achieve what I'm asking? Also, the number can't be static as the work assigned will change based on work available.
    Wednesday, July 27, 2016 7:46 PM
  • Alright Ken...While I'm sure that answer would work, it's way above my pay grade. Is there a simpler way to achieve what I'm asking? Also, the number can't be static as the work assigned will change based on work available.

    Hi. Pardon me for jumping in... But if we could put aside the requirement for a dynamic number of records to assign for a minute, can you please post the SQL you would use to assign all work to the selected worker? Thanks.
    Wednesday, July 27, 2016 8:46 PM
  • Alright Ken...While I'm sure that answer would work, it's way above my pay grade. Is there a simpler way to achieve what I'm asking? Also, the number can't be static as the work assigned will change based on work available.

    As Einstein is sometimes quoted as saying, though in fact he never did per se, you can make something as simple as possible, but no more so.  I don't see that there is any way in which you can make the task you are attempting fundamentally easier.  I have suggested that a table, Assignments, is used to model the relationship type.  It would be possible to do without this table and, with my simple example model, include an EmployeeID foreign key column in the tasks table to represent the employee tom whom a task is assigned.  The reason why I suggested an additional table to model the relationship type is that, without it the EmployeeID column would be Null in those rows where the task is as yet unassigned.  Null is semantically ambiguous, i.e. it has no explicit meaning, so, while we interpret the Null as implying an unassigned task, that is merely an interpretation and not a 'fact'.  This question crops up in the academic literature from time to time, and the solution of modelling the relationship type by a table to eliminate the Null foreign keys is the generally accepted solution.  The model could be simplified by the omission of the Assignments table and the acceptance of Null foreign keys, however, without unduly impairing the functionality of the database.

    With this modest amendment to the model the process of assigning tasks to employees would not change greatly.  It would merely be a matter of updating the tasks table rather than inserting rows into the Assignments table, so it doesn't really gain anything worthwhile.

    Regarding the number being static, I did cover this point in my original reply when I said "the maximum value of the variable n would of course be obtained from the user in reality".  My example used constants for the number and for the EmployeeID value for simplicity.  In an application the INSERT INTO statement would be built in code which references controls in a form where the values of the controls are the EmployeeID and the number of tasks to be assigned to the employee.  It would not be difficult to amend the code to do this.

    So, the bottom line I'm afraid is that I don't really see scope for a significantly simpler solution that that which I've described.  Access is a development environment, and to use it beyond the trivial does require a modicum of development skills.

    BTW, for the record, what Einstein actually said was:

    It can scarcely be denied that the supreme goal of all theory is to make the irreducible basic elements as simple and as few as possible without having to surrender the adequate representation of a single datum of experience.

    Albert Einstein "On the Method of Theoretical Physics" The Herbert Spencer Lecture, delivered at Oxford (10 June 1933)

    Ken Sheridan, Stafford, England

    Wednesday, July 27, 2016 10:03 PM
  • Thanks my friend!  I will try my best to apply the solution that you have provided.
    Wednesday, July 27, 2016 10:56 PM