Field Validation Based on Field in Another Table

[diofree]

Hi guys,
I have looked and looked and seen various threads like this but none that really answered my question and I want to determine if i'm doing something that is not recommended in some way.

I am doing a simple mockup of an HR solution where I have two simple tables:
-Positions (where I have position information including a salary MIN and MAX)
-Employees (where I have employee information as well as actual salary)

As you may have already guessed, I want a validation rule to always ensure that the selected salary is always within the min/max range of that position. I can get this to work without issue when using a Form, however, it seems a user could get around this validation if they input the data directly into the table. Is there any way to add the validation rule at the Table level as well?

Thank you kindly!

 

[JHB]

Normally interacting with users is done using forms.
I think you make yourself unnecessary concerns!
Because add the validation rule at the table level doesn't secure anything for those who want to bypass the system. They could easy take out the validation rule or screw up the min/max values.

 

[MarlaC]

You can add field validation at table level if your database back end is SQL Server (though it is not a simple process). With an Access back end, the only table level validation is whether entry is required and data type (Access doesn't allow text in a numeric field, for example). You'll probably have better luck simply hiding the tables from your users. Most won't dig deep enough to figure out how to get around that.

 

[diofree]

Appreciate it, thanks Marla. I'll investigate the hiding option.

 

[MarlaC]

You're welcome. Good luck with your project.

 

[stopher]

You can enter table level validate like this:

ALTER TABLE tblEmployee ADD 
   CONSTRAINT salary_check 
      CHECK ( 
             EXISTS
                      (
                              SELECT tblPosition.PositionID
                              FROM tblPosition
                              WHERE tblPosition.PositionID=PositionID AND tblEmployee.EmployeeSalary>tblPosition.MinSalary AND tblEmployee.EmployeeSalary<tblPosition.MaxSalary
                       )
            );

See my post #11 in this thread for more info.

 

[sneuberg]

You can enter table level validate like this:

ALTER TABLE tblEmployee ADD 
   CONSTRAINT salary_check 
      CHECK ( 
             EXISTS
                      (
                              SELECT tblPosition.PositionID
                              FROM tblPosition
                              WHERE tblPosition.PositionID=PositionID AND tblEmployee.EmployeeSalary>tblPosition.MinSalary AND tblEmployee.EmployeeSalary<tblPosition.MaxSalary
                       )
            );

See my post #11 in this thread for more info.

Doesn't this require a SQL Server backend? I ask because https://support.office.com/en-gb/ar...eference-af00c501-7972-40ee-8889-e18abaad12d1 implies ANSI-92 is applicable to that situation

 

[stopher]

Doesn't this require a SQL Server backend? I ask because https://support.office.com/en-gb/ar...eference-af00c501-7972-40ee-8889-e18abaad12d1 implies ANSI-92 is applicable to that situation

No it does not require SQL Server back end. But yes it does require ANSI 92. You can set this in the Access database settings. Be mindful of the impact of changing to ANSI 92.