Update 2 related fields with 1 query 2

[BvCF]

Have two tables; Employees and Accounts. The fields within the Employees table are Employee_ID, Username, Password, Access_Level. The fields within the Accounts table are AcctOrig, AcctCurrent, AcctNo, AssignedTo, and Username.

Is it possible to use an update query to populate the "Username" field in the Accounts table and have the corresponding value for Employee_ID to populate the "AssignedTo" field in the Accounts table?

For example, something like;

Update Accounts
Set Username = [Enter User Name], AssignedTo = ?
where AssignedTo = ""
and AcctCurrent = [Enter Acct]

I have 4 employees (Sally, Kim, Sherry, and John) and the following accounts X90, X91, X92, X93, X94, X96, X98.

Sally is responsible for X90 and X91; Kim is responsible for X92; Sherry is responsible for X93 and X94 and John is responsible for X96 and X98.

Another thought is that maybe a update query with a case statement?? This may not be possible.

thanks for any assistance.

 

[PHV]

Why are you storing a derived value (Username) in the Accounts table ?
Have a look here:

http://r937.com/relational.html

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 did review the information concerning proper design of a database.

Additional context:
Currently, I am using the following two queries to limit access to account data within the database;

Query Record Source for the main form
Select Accounts.AcctOrig, Accounts.AcctCurrent, Accounts.AcctNo, Accounts.AssignedTo
from Accounts
where ((Accounts.AssignedTo) in (select employee_id
from Access_List) Or (Accounts.AssignedTo = 0));

Below is the SQL code for Access_List query referenced above;

Select Employees.Employee_ID
From Employees
Where ((Employees.Employee_ID) = get_global ('employee_id'))) OR (((get_global ('access_level')) = "M"));

"Employee_ID" and "AssignedTo" are of a number data type.

My Question:
How do I go about assigning accounts to the employees and display the username in the AssignedTo field on my main form?

How would you do differently?

 

[lespaul]

Employees table:
Employee_ID,
Username,
Password,
Access_Level.


Accounts table:
AcctOrig,
AcctCurrent,
AcctNo,
AssignedTo,
Username

the red bolded field above should be removed. AssignedTo (store the same value from Employees.EmployeeID) is the foreign key to Employees. To then get the name of the person associated with the EmployeeID in the Accounts table you JOIN the two tables on employeeID/AssignedTo as shown below:

SELECT AcctOrig, AcctCurrent, AcctNo, Accounts.AssignedTo, Employees.UserName
FROM Accounts
INNER JOIN Employees on Accounts.AssignedTo = Employees.EmployeeID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

I'd probably use a combobox.

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

 

[BvCF]

lespaul,

Upon review, I deleted "Username" from the Accounts table.

Maybe I am missing something here, How do I go about assigning new accounts to specific employees if I only know the EmployeeID without knowing the "crossmap" between EmployeeID and Username within the Employee table?

Based on what was stated, it appears that the update query should be;

Update Accounts
Set AssignedTo = ?
where AssignedTo = ""
and AcctCurrent = [Enter Acct]

The Manager needs to assign accounts to specific employees on a daily basis.

So, if the Manager wants to assign 10 accounts with a X90 Account to Sally, how would the SQL be displayed?

Then, when Sally arrives the next day, she should only see the 10 accounts that were assigned to her the prior day and any other encounters that were previously assigned.

Further, I want to display "Sally" on the main form.

How would I do that?

Thanks for the patience. Just trying to grasp the concepts.

 

[lespaul]

you can put a combo box on the form that the manager uses to assign the accounts. This combo box can hold both the EmployeeID and UserName, but only display UserName. When the Accounts record is inserted you would capture the EmployeeID from the selected row in the combo box to update the table.

Leslie

 

[BvCF]

Okay,

I have a combo box on the form and a listbox that displays all of the unassigned accounts.

But, I would like to assign all accounts on a daily basis and provide Management with the ability to assign/reassign accounts using the combo box approach. For example, the daily assignment logic would be something like;

Private Sub Update()
If AssignedTo = ""
Case Account in (X90, X91)
Update Accounts
Set AssignedTo = "Sally"
Case Account = X92
Update Accounts
Set AssignedTo = "Kim"

and so on
End Sub

Then, Management would also have the option of using the combo box approach by selecting the unassigned accounts within the listbox, clicking the combo box (that displays the person who is being assigned the accounts) and then hitting a command button to actually update the "AssignedTo" and "DateAssigned" fields on the Accounts table.

Am I way out in left field? Is this even possible?

 

[lespaul]

except you don't want to set AssignedTo = "Sally", you want to set AssignedTo = Sally's EmployeeID number.

 

[BvCF]

Oops! You are right, I need to set AssignedTo = the Employee Id number.

Is this type of query possible? If so, would you be able to provide a clue as to how it would be constructed?

Therefore, to display the username on the form, what should go in the control source for the field with the lable "AssignedTo?"

Thanks in advance.