Data Validation

[LC]

Hi,

I've been dealing with the following problem:

1) Users generates monthly information in Excel with the following
format

Volume Table
==========
Product Jan Feb Mar
SKU1 10 0 30
SKU2 5 7 20
SKU3 0 0 5

Price Table
========
Product Jan Feb Mar
SKU1 0.3 0.3 0.3
SKU2 0.5 0.0 0.7
SKU3 0.0 0.1 0.1

I want them to enter this information (using copy and paste) in an
Access Form in order to multiply Volume table x Price Table.

Before calculating the revenue, the access form must validate the
following conditions:

a. Validate that the SKU exists in Master Data (done)
a. If there's volume, there should be price. (e.g SKU2, in Feb has 7
in volume but 0 in price; this is an error
b If volume is zero, price is not required. (assume that Nulls are
not accepted)
c. paint which are the fields with problems

I think the best option is to change the format and work with
transposed volume and prices tables to perform the validation.

Volume Table
Product Month Volume_Amt

Price Table
Product Month Price_Amt

and query that information on Product and Month Fields.

I''ve seen this kind of validation in a VB Form with a Grid Control,
but can it be performed only with MS Access?

Regards,

LC

 

[John Spencer]

Yes, and it should be fairly easy to do with the normalized structure you are
proposing.

SELECT Volume.Product, Volume.Month, Volume_Amt, Price_Amount
, IIF(Price_Amt = 0 or Price_Amt is Null AND Volume_Amt > 0,"Invalid Price")
as ErrorMessage
FROM Volume LEFT JOIN Price
ON Volume.Product = Price.Product
AND Volume.Month = Price.Month

'Add a where clause if you want to return only records with problems
WHERE (Volume_Amt <> 0 and Volume_Amt is not Null)
AND (Price_Amt = 0 or Price_Amt is Null)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County