Remove the Active field!
It sounds like it is completely dependent on the foreign key + Date field,
i.e. if it were True for a record that was not the latest date, that would
be an error. It therefore violates the most basic normalization rules, and
constitutes a maintenance nightmare you don't need.
The better solution is to get Access to choose the most recent value for
you. This kind of thing:
SELECT Salary.*
FROM Salary
WHERE SalaryDate =
(SELECT Max(SalaryDate) AS MaxOfSalaryDate
FROM Salary AS Dupe
WHERE Dupe.SzemelyId = Salary.SzemelyId)
If subqueries are a new idea, see:
http://allenbrowne.com/subquery-01.html
Also useful:
http://www.mvps.org/access/queries/qry0020.htm
For this solution to work well, consider these changes:
1. Rename the Date field. Date is a reserved word in JET SQL (where JET may
foul up trying to interpret the query) and also in VBA (where it will be
misunderstood for the system date.) Say you choose SalaryDate. More on
reserved words:
http://allenbrowne.com/AppIssueBadWord.html
2. Open the query in design view, and make these changes so there can be no
question about which one is actually the most recent salary date:
2.1 Make the foreign key field Required.
(Also, remove the zero default value if Access assigned that.)
2.2 Set the Required property to Yes for the SalaryDate as well.
2.3 Create a unique index on the combination of foreign key and date:
2.3.1 Open the Indexes box (toolbar.)
2.3.2 On a blank row, enter an index name such, and the first field.
2.3.3 In the lower pane of the Indexes dialog, set Unique to Yes.
2.3.4 On the next row, leave the index name blank (indicating it's part of
the same index), and enter the other field. The dialog will now look
something like this:
SalarydateSzemelyid SalaryDate Ascending
Szemelyid Ascending
2.3.5 Save the changes to the table.
Hope that helps.