Table1 - Employee
... EmployeeID Name Title ...
... 1 ZZ engineer ...
... 2 LL engineer ...
Table2 - Site
... EmployeeID Location Date ...
... 1 BJ xxxx ...
... 1 TJ xxxx ...
... 2 BJ xxxx ...
... 2 SH xxxx ...
Query1 - Employee & Site
Location Title Count
BJ Engineer 2
TJ Engineer 1
SH Engineer 1
When employee change position, employee title changed. For example, ZZ upgrade to Manager. The Query1 can not show "BJ Engineer" as Count 2.
Solution1:
To record title in Table2, Query1 is only about Table2 - Site.
Solution2:
To record transfer date somewhere, and cheange Query as needed.
Which of the above solution is better?
... EmployeeID Name Title ...
... 1 ZZ engineer ...
... 2 LL engineer ...
Table2 - Site
... EmployeeID Location Date ...
... 1 BJ xxxx ...
... 1 TJ xxxx ...
... 2 BJ xxxx ...
... 2 SH xxxx ...
Query1 - Employee & Site
Location Title Count
BJ Engineer 2
TJ Engineer 1
SH Engineer 1
When employee change position, employee title changed. For example, ZZ upgrade to Manager. The Query1 can not show "BJ Engineer" as Count 2.
Solution1:
To record title in Table2, Query1 is only about Table2 - Site.
Solution2:
To record transfer date somewhere, and cheange Query as needed.
Which of the above solution is better?