Validation Rule

[ritzzy]

Hello everybody!

I am trying to create a database in which to put all the invoices received from the suppliers.
For this I want to have the first table named "contracts". Here I want to register all the articles of the suppliers with their prices.
For the next tables in my database I want to use the articles from the first table and for the price field I want a validation rule which not allow me to enter another prices than those used in the first table.

Any idea is welcomed!
<!-- / message -->

 

[boblarson]

My first question is WHY - why would you be duplicating this data into another table? That strikes me as a bad, non-normalized, database design. So, what is the purpose?

 

[ritzzy]

In the fist table, named "contracts" I want to enter each contract I have with the suppliers. Here, most important are the articles and their prices ( each article has its price).
In the next table, named "invoices" I want to enter the articles from the received invoices. In order to avoid having different prices than those negociated in "Contracts", I want a validation rule which will forbid me to enter another price. But first I think I need to establish a link between each article and its price. Is it possible?

 

[xenou]

Are you actually typing this into a table? What kinds of prices are on the invoices?

It somewhat begs the question - if the invoices have prices already then you really want to compare them to the contract prices to see if they are at variance. Generally, you need your invoice history to have the actual invoice amounts (unless you routinely ignore these and shortpay your invoices anyway). And these should be stored since prices change over time, so you do need a record of the price charged at the time the invoice was entered.

Use a form for your input so you can update the fields in the invoice table with information from the contracts table (whether this is the actual price field or just a check or another field to show that the price matches the contract price).