LAST job ?!

[Mr.Access]

hi Friends ,

I have three tables :

1) Employee :
* Emp_No
*Emp_Name
* ………..
2) Job
*Job_No
* Job_Name
* ……………

Since the relation is (M:M) , that may an employee transfer from a job to other, the third table generated .
3) Org_Chart
* Emp_No
*Job_No
*Date
Now,
How can I get just the LAST job when I query about the Employees’ jobs ?

Kind Regards,,

 

[rainman89]

use a dmax function on the date assuming that the latest date will be that of the latest job

 

[Mr.Access]

2nd Step ..

I've got your idea rainman89 .

but the query couldn't be created as the desired ?

I made the following fields :

* Emp_No1 (from Employee tbl)
* Emp_Name (from Employee tbl)
* "Last_Date": (Hidden)
=DMax("[Date]","Org_Chart","[Emp_No]=[Emp_No]1")

* Job_No (from job tbl)
* Job_Name (from job tbl)

Emp1 is a field in the query but An Error apears with the query result since it Emp1 doesn't belong
to the (Org_Chart tbl) !

What can I do ?

 

[Mr.Access]

I got it !

using the ( "Total" >> LAST ) in the query design for the job table solved the problem .

Many thanks rainman89 .