Verify data via query

[LC]

Hi,

I'm looking for an easy way of performing the following verification:

Table1
Volume information
--------

SKU_CD Year Territory Jan... Dec
abc 2011 South 10 30

Table2
Price information
---------------------
SKU_CD Year Territory Jan... Dec
abc 2011 South 0.5 0

Which is the easiest method to perform these validations:

A) if volume exists, then price must exist. In Jan there' volume and
price, but in Dec, there's volume but there's not price

B) Pricing table can have more rows than volume table. I only want to
display Volume table information (SKU, year, territory) and the
corresponding matching price.

C) there can be prices that are not in the catalog. I have to
construct another query showing which products (volume table) don't
have a corresponding price associated.

Note: combination of SKU, year and territory fields is unique in both
tables.

Any help will be appreciated.

Regards

 

[John W. Vinson]

Hi,

I'm looking for an easy way of performing the following verification:

Table1
Volume information
--------

SKU_CD Year Territory Jan... Dec
abc 2011 South 10 30

Table2
Price information
---------------------
SKU_CD Year Territory Jan... Dec
abc 2011 South 0.5 0

Which is the easiest method to perform these validations:

A) if volume exists, then price must exist. In Jan there' volume and
price, but in Dec, there's volume but there's not price

B) Pricing table can have more rows than volume table. I only want to
display Volume table information (SKU, year, territory) and the
corresponding matching price.

C) there can be prices that are not in the catalog. I have to
construct another query showing which products (volume table) don't
have a corresponding price associated.

Note: combination of SKU, year and territory fields is unique in both
tables.

Well, this table design makes your task much harder. You're storing data - a
month - in a fieldname, rather than as a value in a field. This makes sense in
a spreadsheet, but it's not good for a relational database! As it is, you will
need to do either twelve queries, one for each month, or a monstrously
complicated (and possibly nonfunctional) join, comparing all twelve fields. A
much better structure would to have both tables combined, structured like:

SKU_CD SaleDate Territory Volume Price
abc #1/1/11# South 10 0.5
abc #12/1/11# South 30 0

Since volume and price aren't really independent, it would make sense to me to
combine them in one table.

Failing that, you can create a query by adding both tables to the query grid.
Join Volume to Price Information by dragging SKU_CD to SKU_CD, [Year] to
[Year] (note that Year is a reserved word and a bad choice of fieldname), and
Territory to Territory. Select each join line in turn and choose Option 2 -
"Show all records in Table1 and matching records in Table2". Select the SKU,
date, territory and Volume fields from Table1, and the Price field from
Table2. Put a criterion on Volume of

0

and on Price of

=0 OR IS NULL

This will find all records which have a nonzero volume and either a zero
(which is not "no value", zero is a perfectly valid number) or which have no
record at all in the price table.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com