Standard value in form based on several tables

[RainLover]

Bart

Do you have an effective Date. All wages are going to increase from time to time. If you are paying someone on Level AA1 which might be 45.67 then one day this level of AA1 may increase to 46.23.

Have you thought about going back in history to see what value was paid in the preceding periods. I don't see anything in your tables that will help.

 

[BartBE]

Indexation is indeed an effective date. The goal of this field is, like you say, to keep a history. Aside from the ID field, the table BAREMAS looks like this:

INDEXATION - SCALE - SENIORITY - WAGE
1/7/2011 - A145 - 0 - 2000€
1/7/2011 - A145 - 1 - 2025€
....
1/7/2011 - B101 - 0 - 1500€
...
1/7/2011 - B101 - 25 - 1915€
...
1/3/2012 - A145 - 0 -2040€
1/3/2012 - A145 - 1 - 2065,5€
...
1/3/2012 - B101 - 0 - 1530€
...

 

[RainLover]

Very good. That appears to be correct.

I know you don't like the idea but you may have to use VBA Code to retrieve the correct rate. But as you have the table correct you can worry about that later.

 

[BartBE]

I just realized that the wage should not correspond to the most recent indexation. It should instead correspond to the indexation that was in effect at the starting date of the employment. This means I need to adjust my initial question:

I need the wage that corresponds to

• the function's barema,
• the proper seniority,
• and the indexation that was in effect at the starting date of the employment (one employment = one record in WN_Tewerkstellingen)

I apologize for the confusion as a result of this change. Like I said, I realized this just now.
On a side note: I will be out of office and not replying for a week.

An example to clarify. (Skip if you understand what I already wrote.)

1. Suppose that an indexation is announced today (10th of September). It will be implemented/take effect in three months (10th of December). I immediately (today) add the indexed wages to the BAREMAS table. The "future" wages will be considered as the most recent.
2. Later today, so still three months before the future indexation takes effect, a colleague is promoted. He takes up his new job immediately. This means I need to add a new record to the subform WN_Tewerkstellingen (employments) that has starting date today (10th of Sep).
3. The wage that will be suggested/looked up by the form (had my initial question been resolved) would have been that of the indexation of the 10th of December. (Assuming determining the most recent indexation would involve max(indexatie).) Which is of course wrong. For the period 10th of Sep - 9th of Dec, I need the wage that corresponds with the indexation that is in effect on the 10th of September, which is not the most recent according to Access.
4. To finish this example: on the 10th of December, a new employment record (WN_Tewerkstellingen) will be created to reflect the salary adjustment as a result of the indexation. This new record will contain the same information as the one I created on the 10th of September, except for "Indexation" instead of "Promotion" in the GEBEURTENIS (event) field and of course a different salary.

 

[BartBE]

Solved it. Thanks to the kind of help of someone who has greater knowledge of VBA than I do.

The solution, if anyone is interested:

• Turned the field "Anciënniteit" in the table WN_Terwerkstellingen into a dropdown field with limited values. (I don't know the exact name in English.)
• In the subform WN_Tewerkstellingen, I put the following VBA code in the after update section of the "Anciënniteit" field.

Private Sub Anciënniteit_AfterUpdate()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
On Error Resume Next

If Len(Trim(Me.Startdatum & "")) = 0 Then
MsgBox "Eerst een startdatum invullen"
Me.Anciënniteit = Null
Me.Startdatum.SetFocus
GoTo 1:
End If

If Len(Trim(Me.Functie & "")) = 0 Then
MsgBox "Eerst een functie kiezen"
Me.Anciënniteit = Null
GoTo 1:
End If

strSQL = "SELECT Lonen.Loon FROM Lonen INNER JOIN Functies ON Lonen.Barema = Functies.Barema " & _
" WHERE Functienaam = '" & Me.Functie & "' AND Lonen.Indexatie = " & _
"(SELECT Max(Lonen_1.Indexatie) FROM Lonen AS Lonen_1 WHERE Lonen.Barema = Lonen_1.Barema " & _
" AND Lonen_1.Indexatie <= # " & Format(Me.Startdatum, "mm-dd-yyyy") & " # AND Lonen_1.Anciënniteit = " & Me.Anciënniteit & " AND Lonen_1.Anciënniteit = Lonen.Anciënniteit) "

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst
rst.MoveLast
If rst.RecordCount <> 0 Then
    If rst.RecordCount = 1 Then
    Me.Brutomaandloon = rst!Loon
    Else: MsgBox "De door u gekozen combinatie van functie en anciënniteit levert MEERDERE mogelijke lonen op. (De indexatie wordt automatisch gekozen o.b.v. de startdatum van de tewerkstellingsperiode.) Kijk de tabel 'Lonen' na."
    End If
Else: MsgBox "De door u gekozen combinatie van functie en anciënniteit levert GEEN loon op. (De indexatie wordt automatisch gekozen o.b.v. de startdatum van de tewerkstellingsperiode.) Kijk de tabel 'Lonen' na."
End If

rst.Close
Set rst = Nothing
Set db = Nothing

1:
Exit Sub

End Sub