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

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

On 2/9/2011 2:56 PM, LC wrote:
> 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
>
>
>
>
>