I may be over thinking this?

[dcavaiani]

Table has fields costpereach, costpercase, and amountpercase. I want to add a [calc] text field which says either "Yes" or "No", and is based on the above 3 fields.

So, at the table level, I use the following validation rule:

[calc]=IIf((([costpercase]/[amountpercase])=[costpereach]),"Yes","No")

But, I don't see it working, nor do I know if this is even a valid attempt?

 

[Ranman256]

no if needed, it returns true/false , or format it yes/no

[calc]=([costpercase]/[amountpercase])=[costpereach]

 

[dcavaiani]

Thanks, but that still does not do it ... "expression typed incorrectly or too complex ....." I am using Access 2002.

 

[gemma-the-husky]

you definitely do not want to store this at the table level. just evaluate it whenever you need it.

couple of issues

cost/amount: if amount is zero you will get a divide by zero error
cost/amount: once you start getting into "real" numbers - ie decimal expansions - you will get conversion errors.

eg £10.00/100 items = 10p per item

now, digital systems cannot store 0.10 (ie 10p) exactly - so just undertaking the check.

if £10/100 = 0.10 MAY give a false error. It may not, but you just have to be very careful when testing equivalence of real numbers.

If you want to do this, the best way is to
a) store costpercase and costpereach as currency
b) do it as a multiplication test
c) include nz to deal with nulls

something like this

samevalue = (nz(costpercase,0) = nz(amount,0) * nz(costpereach,0))

 

[dcavaiani]

Both suggested solutions seemed to work, it was just that this was indeed a bad idea to begin with, and not really helpful at the Table level.

 

[dcavaiani]

SELECT item, costpereach, costpercase, amountpercase
FROM gmcostmaster
WHERE (nz(costpereach,0)<>(nz(costpercase,0)/nz(amountpercase,0)));

Now trying sql, but get overflow ?

 

[gemma-the-husky]

you are probably dividing by zero.

that's why I suggested using multiply.

 

[dcavaiani]

The multiply sure seems to get past that error. I may retry again at the Table level. Sure seems like this should be a bit easier to get!

 

[dcavaiani]

Boy, how frustrating. I copied my table, applied this:

[samevalue]=([costpercase]=[amountpercase]*[costpereach])

which put a value of "No" in appropriate places in that samevalue field.

Now, i try to do exactly the same in the original table and all i get is blanks!!
;

 

[gemma-the-husky]

to repeat.

it is a mistake to store this in the table.
you don't even need to store all 3 values

Al you need to store is EITHER
a) cost per case
b) items per case

OR
a) cost per item
b) items per case

OR EVEN
a) cost per item
b) unit type indicator

and then in another table
a) unit type indicator
b) items per unit
then you can add additional things like
c) allow partial unit.

(so if you price eggs at £1.00/dozen, you can sell 6)

 

[dcavaiani]

to repeat.

it is a mistake to store this in the table.
you don't even need to store all 3 values

Al you need to store is EITHER
a) cost per case
b) items per case

OR
a) cost per item
b) items per case

OR EVEN
a) cost per item
b) unit type indicator

and then in another table
a) unit type indicator
b) items per unit
then you can add additional things like
c) allow partial unit.

(so if you price eggs at £1.00/dozen, you can sell 6)

I really appreciate that you are the main resource for me on this one! I know your suggestion is following the normalization rules to the tee. In this case, I am just going to store all the data in the one table and go from there. I dropped the validation and just carry the values and show a difference field. In this users case, the unit price may NOT be kept up to actually perfectly equal the stated case price divided by the stated case quantity. I know that SHOULD likely be - but the accuracy is just not required/needed as of now. THANK YOU MUCH!