以最大值更新表 [英] Update a table with maximum

问题描述

嗨.. 

我有一个包含2个字段的表：名称和工资

该表重复了相同的内容姓名不同的工资。

我需要该字段所有记录中的工资都更新为每个人的最高工资......例如：

原始记录

Peter - 100

彼得 - 150

彼得 - 200（彼得最高）

约翰 - 200

约翰 - 500（约翰的最大值）

安妮 - 600（安妮的最大值）

< span style ="text-decoration：underline"> 更新后我会获得：

Peter - 200

Peter - 200

Peter - 200

John - 500

John - 500

Anne - 600

提前致谢！

解决方案

As一般来说，你应该n您可以在表格中导出/计算商店值，而是在需要在表单和报表上显示这些值时使用查询来进行计算。

在您所描述的表格中，我还建议添加一个SalaryDate字段来指示输入每个工资记录的时间。 

要显示员工最高工资，您可以在查询中使用MAX函数，然后将该查询用作表单或报告的记录源：

 SELECT EmployeeName，MAX（Salary）AS MaxSalary 
 GROUP BY EmployeeName 

此外，更好的表结构会为员工和工资设置单独的表格如下：

 tblEmployees：
 
 EmployeeID  -  AutoNumber / Primary Key 
 FirstName 
 LastName 
 DateHired 
描述员工所需的任何其他字段

 tblSalaries： 
 
 SalaryID  - 自动编号主键
 EmployeeID  - 将工资记录链接到员工
薪资 - 货币字段
 SalaryDate  - 给出日期工资/募集

此结构（或类似）允许您为每位员工提供多个薪资记录，显示薪资历史记录。 然后，您可以使用查询来报告特定工资信息，例如最高工资，当前工资（可能与最高工资不同），
起薪，提高金额等等...

Hi.. 

I have a table with 2 fields: Name and Salary

The table has repeated the same name with different salaries.

I need that field Salary is updated in all the records to the MAXIMUM salary that each person has had..  example:

Original records

Peter - 100

Peter - 150

Peter - 200 (max for peter)

John - 200

John - 500 (max for john)

Anne - 600 (max for anne)

After Update I would obtain:

Peter - 200

Peter - 200

Peter - 200

John - 500

John - 500

Anne - 600

Thanks in advance!

解决方案

As a general rule, you should not store values that you can derive/calculate in your tables, but rather use queries to make those calculations when you need to display them on forms and reports.

In the table you have described, I'd also suggest adding a SalaryDate field to indicate when each salary record was entered. 

To display an employees maximum salary, you would could use the MAX function in a query, and then use that query as the recordsource for your form or report:

SELECT EmployeeName, MAX(Salary) AS MaxSalary
GROUP BY EmployeeName

Also, a better table structure would be to have separate tables for employees and salaries as follows:

tblEmployees:

EmployeeID  -- AutoNumber/Primary Key
FirstName
LastName
DateHired
Any Other Fields needed to describe employees

tblSalaries:

SalaryID  --  Autonumber Primary Key
EmployeeID -- This links the salary records to the employees
Salary -- Currency field
SalaryDate -- Date salary was given/raised

This structure (or similar) allows you to have multiple salary records per employee, showing a pay history.  With that, you can then use queries to report specific salary info such as Max Salary, Current Salary (which might be different from the max salary), starting salary, Raise amounts, etc etc etc...

这篇关于以最大值更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！