update query with DMax?

[jfeketet]

Hi All
I have to tables and in the first one I have a key (SzemelyId) and in the
other table Salary: SzemelyId, Salary, Date, Active) is Szemely Id is foreign
key. Each SzemelyId has one or more "Salary" and I would like to make an
Update query which select every person last (max) salary and make the
"Active" checkbox is TRUE. Just for the max salary.
Thanks for your help
Janos

 

[Allen Browne]

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.

 

[John Spencer]

First set all Active to FALSE

UPDATE Salary
SET Active = False
WHERE Active = True

Second, now set Active to True for selected records
UPDATE Salary
SET Active = True
WHERE Salary.Date =
(SELECT Max(Tmp.Date)
FROM Salary as Tmp
WHERE Tmp.SzemelyId = Salary.SzemelyId )

NOTE: Backup your data first. That way if this fails to do what you want
you can set the data back to its original state and try something else.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jfeketet]

Thank you very much your quick answers. I will try them

 

[jfeketet]

It works! Thank you very much all of you

First set all Active to FALSE

UPDATE Salary
SET Active = False
WHERE Active = True

Second, now set Active to True for selected records
UPDATE Salary
SET Active = True
WHERE Salary.Date =
(SELECT Max(Tmp.Date)
FROM Salary as Tmp
WHERE Tmp.SzemelyId = Salary.SzemelyId )

NOTE: Backup your data first. That way if this fails to do what you want
you can set the data back to its original state and try something else.

Hi All
I have to tables and in the first one I have a key (SzemelyId) and in the

[quoted text clipped - 5 lines]

Thanks for your help
Janos