defining a constraint in relation to another piece of data

[peeryowen]

I have agreed to help someone create their database in Access and it has been a long time for me. They are a construction company.

In a table called WorkType we have the following fields, IdWT, NameWT, CLrateWT, EMrateWT, and a foreign key of idScope.

CLrateWT is the rate they bill clients for a certain type of work.

EMrateWT is the rate they pay their employees for a certain type of work.

The difference between these two is of course the margin and where they make money.

In this table I think it would be good to set a constraint on the data field CLrateWT. I would like to set the constraint to say that CLrateWT > EMrateWT so that it is impossible to enter data where what clients pay for a certain type of work is less than what employees are paid for that same type of work. ie clients pay less than the company pays employees so they lose money on the job.

When I created the table I can't figure out how to add a constraint to the field CLrateWT that references the value in the field EMrateWT.

Any help would be greatly appreciated.

 

[PHV]

Use a TABLE validation rule

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[peeryowen]

Thanks for the help that is exactly what I needed and in fact I am an American living in France and I am trying to navigate between French and English versions and versions 2003 and 2007 and since I have not done Access in a while I was lost.