Employees only able to see accounts reassigned to them 1

[BvCF]

Have a Access Db that approximately 8 employees use. I only allow the employees to view his/her accounts based on their username as displayed in the "AssignedTo" field.

For example, the control source for the textboxes on the main form is a query similar to the following;

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName,tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAllAccounts.AssignedTo) in (select employee_id from Employee_List) or
(tblAllAccounts.AssignedTo)=0));


Now, the challenge is when the Manager desires to maintain a history of the assignment of accounts. In other words,
accounts can be assigned several times - when a employee retires, terminates or goes on an extended vacation.

Therefore,I need to add several additional fields such as

ReassignedTo1
DateReassignedTo1
ReassignedTo2
DateReassignedTo2
ReassignedTo3
DateReassignedTo3

For example, Mary was initially assiged to accounts 1, 2, and 3. If Mary terminates, the accounts that were initially assigned to Mary should be re-assigned to Sally. If Sally goes on extended leave, the accounts then should be re-assigned to John.

Therefore, how should the query stated above be modified so that the reassigned person is the only person that will be
able to view the accounts. The person that was assigned the account previously should not be able to view the account once it has been re-assigned.

It appears that I need to include "ReassignedTo1", "ReassignedTo2", and "ReassignedTo3" in the query similar to the
following;

Update tblAllAccounts
Set "ReassignedTo1" = "Sally"
where "AssignedTo" = "Mary"
and DateReassigned1 = sysdate

Thanks in advance for any insight to this problem.

 

[Remou]

I think that the design could be reconsidered, it would be simpler if the "assigned to" data were in a separate table. The fields might include:

tblAssigned
AccountNumber
AssignedTo
AssignedFrom
Date
Current (y/n)

This way the current assignee could always be found, yet the assignment history would be maintained.

 

[BvCF]

Remou,
Excellent Point!

Then, how would the following script be modified to only allow the current assignee to view his/her accounts?

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName,tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAllAccounts.AssignedTo) in (select employee_id from Employee_List) or
(tblAllAccounts.AssignedTo)=0));

My initial attempt would be something like;

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName,tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAssigned.AssignedTo) in (select employee_id from tblAssigned.Current = "y";

Should the field "current" be on the table titled "Employee_List?"

 

[Remou]

I would see AssignedTo as an employee id, so the employee table may not be necessary. I am not sure where employee ID is coming from, let us say it is a control on your form, so:

[tt]SELECT b.AccountOrig, b.AccountCurrent, b.AccountNo, b.LastName,b.FirstName, b.TotChg,
FROM tblAllAccounts AS b
LEFT JOIN
[Select tblAssigned.AccountNo, tblAssigned.AssignedTo
FROM tblAssigned
WHERE Current=True]. AS a
ON b.AccountNo = a.AccountNo
WHERE a.AssignedTo=Forms!frmForm!EmployeeID
OR a.AccountNo Is Null;[/tt]

If you see what I mean.

 

[BvCF]

Still tackling this!

"Employee_id" is on the Employee_List table. For example, the fields on the Employee_List table are:

Employee_id---Username---Password---Access_Level
1-------------MaryB------*****------E
2-------------JanC-------*******----M
3-------------TomV-------*****------E

The Record source for the main form is below that only allows for the end user to only see his/her accounts i.e. "AssignedTo" value equals the "employee_id" value. Note, the employee types in their username and password upon entering the application. If I now populate "ReassignedTo" and "DateReassigned" with the employee's name and date that I reassigned the account, respectively, how should I modify the query to only allow the current assignee to be the only person to see the account?

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName,tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAllAccounts.AssignedTo) in (select employee_id from Employee_List) or
(tblAllAccounts.AssignedTo)=0));



Thanks in advance for any insight.

 

[Remou]

I am afraid you have lost me. Where does Reassigned come from? I had visualized a table in which there is only Assigned. I think you should post you table schemas.

This:
Microsoft: Access Tables and Relationships Forum: forum700
Is probably the best forum to post in, because we have moved quite far from forms.