Using most recent Info

[KristenD]

In my database, I have a table that is used as join table for a many to many relationship. I am having trouble using that table in queries in order to use the most recent information entered in there.

For example, the table stores the emp ID and the Job Number where the employee is working. The employee can transfer to different jobs. How do i get the query to pull the most recent entry? I have tried using Max Queries and it still will not work. Is there something I'm doing wrong?

Thank you! This is an Access 2007 database.

 

[nanscombe]

There are a couple of different ways to achieve this that I can think of:

1) You add a date field which you would then use to pick the most recent one. In fact you may want two, a start and an end date. That way the current one should have a start date but no end date.

2) A Yes / No field to tell which record is active

Unless you have an autonumber field there would be no built in order to be able to pick he most recent record.

I'm assuming that the Job number just refers to a particular post within the organisation.

 

[KristenD]

I have a date field in another table that I would like to correspond to the most recent info posted. I think what I have to do is have a junction table for a many to many relationship.

The job number refers to the job sites where the employees are working.

Thank you!!

 

[nanscombe]

I thought this was a junction table (many to many) you were describing?

Employee table -> This junction table <- Job table

Emp Id - Employee Id
Job Number - Where they are working
Start Date - When they joined
End Date - When the left

Otherwise how would you use the date on the other table to tell which was the latest information in this one?

 

[KristenD]

The 2 tables I'm looking to use in the junction table are:

tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumber

tblEmpRating
EmpRatingID <PK>
EmpID2 <FK>
EffectiveDate
SuptID
Status
Craft

The junction table would look like this:

tblEmpRateInfo
RateInfoID <PK>
EmpInfoID <FK>
EmpRateID <FK>

The only other issue I forsee is the tblEmpInfo is only filled out if the employee is active which is in another table. I have a code written behind the forms used for entry for this which is:

Private Sub EmploymentStatus_AfterUpdate()
If Me.EmploymentStatus = "Active" Then
    Me.tblEmpInfo.Visible = True
    
Else
    Me.tblEmpInfo.Visible = False
    
End If
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
    Me.tblEmpInfo.Visible = True
    
Else
    If Me.EmploymentStatus = "Active" Then
        Me.tblEmpInfo.Visible = True
    Else
        Me.tblEmpInfo.Visible = False
    End If
End If