Search code examples
ms-accessinsertincrementrpaautomationanywhere

How can I increment a field value by 1 using SQL Query in MS Access

I'm working on a project using Automation Anywhere Client. I'm extracting data from MS Excel to MS Access using SELECT and UPDATE statements, but it is possible that the data could repeat itself, and in this case, I would like to UPDATE only 3 fields, those are the statements that I'm using

    INSERT INTO $vDiagnosis$ 
    VALUES ('$vID$','$vFirstName$','$vLastName$','$vGender$','$vPhone$','$vAge$',
    '$vDateOfVisit$','$vCondition$','$vInsuranceCo$','$vInsuranceNr$','$vVisitingNumber$')
    
    Update $vDiagnosis$ 
    SET DateOfVisit = '$vDateOfVisit$', Condition = '$vCondition$' 
    WHERE ID = '$vID$'

which are the DateOfVisit and the Condition and the VisitingNumber (which is the number of times someone visited that hospital). The thing is, I'm trying to increment the VisitingNumber every time the UPDATE statement is executed, for example, someone visits today, I want it to be 1, the UPDATE statement is executed again, I want that 1 to increment.

I tried making a variable, and to increment it every time the UPDATE is being executed but whenever it loops and goes to the next INSERT statement, it continues incrementing, even if it inserts a new value.

A preview of the work I'm trying to do on Automation Anywhere

Any help would be appreciated. Thanks in advance!

Solution

  • Not sure about the variable aspect mentioned, but bumping the VisitingNumber count should be able to be done as:

    UPDATE $vDiagnosis$ 
   SET DateOfVisit = '$vDateOfVisit$',
       Condition = '$vCondition$',
       VisitingNumber = VisitingNumber + 1
 WHERE ID = '$vID$'

    To the insert, the value for the visitingNumber could simply be 1 without a variable, like so:

    INSERT INTO $vDiagnosis$ 
VALUES ('$vID$','$vFirstName$','$vLastName$','$vGender$','$vPhone$','$vAge$',
'$vDateOfVisit$','$vCondition$','$vInsuranceCo$','$vInsuranceNr$', 1)

    Or better, perhaps a default value of 1 could be specified at the table level, and then it would not need to be included with the insert query at all. (it would be set to 1 at insert)

    Hope that makes sense.