Conditional Update Query - If Contracts Not In Table 1

[BvCF]

The query that I will use to "auto assign" accounts to the staff (via Windows Scheduler) is below

UPDATE tblVar INNER JOIN tblAssignments ON tblVar.Contract=tblAssignments.Contract SET tblVar.AssignedTo =

tblAssignments.Employee_ID
WHERE tblVar.DateRecordCreated=Date() And tblVar.AssignedTo=Null;


As you can see, I am updating the "AssignedTo" field with the employee's id that is stored in
tblAssignments.


However, there are some contracts that may not be stored in tblAssignments.
For example, if an account on contract "XJJ" is not in tblAssignments but appended to my
main Access table (from Oracle), how can I auto assign all of the accounts on contract "XJJ"
to an administrator whose employee id is 3. Please note, by assigning this to the administrator,
the AssignedTo field is populated with "3", the administrator's employee id. Then, the
administrator will be the only individual to access this account within the Access database.
At that point, the Administrator can reassign the account to another employee.

My intial resolution is to incorporate an if statement within the Update query above.

How would you resolve this? Also, how would you display the username in the "AssignedTo" field
on the main form instead of the numerical "employee id" value that is stored in tblEmployees.


Any assistance would be greatly appreciated.

Thanks in advance.


For Reference, the following are the tables noted above

tblAssignments (Contract,Description,CustomerStaff,Employee_ID)
tblVar (Contract, AcctNo, AssignedTo, Username)
tblEmployees (Employee_ID, Username, Password, Access_Level)

 

[PHV]

First, replace this:
And tblVar.AssignedTo=Null
with this:
And tblVar.AssignedTo Is Null

For the contracts not in tblAssignments, you may try this:
UPDATE tblVar SET AssignedTo = 3
WHERE DateRecordCreated=Date() AND AssignedTo Is Null
AND Not(Contract In (SELECT Contract FROM tblAssignments))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

PHV,

I will try the code and post back.