MS Access Query - Extract records where Cost Per Unit is Different 1

[IngDep]

Using the query below, I am interested in extracting all records from the "CostPerUnit_Supplies" table that have two or more CostPerUnit fields populated and have a different cost per unit in one or more of the following six fields;
Supplies-Jan2007_CostPerUnit
Supplies-Feb2007_CostPerUnit
Supplies-March2007_CostPerUnit
Supplies-Jan2008_CostPerUnit
Supplies-Feb2008_CostPerUnit
Supplies-March2008_CostPerUnit.

Note, the CostPerUnit values should be the same. I am designing the query to extract all exceptions to this rule.

What modifications are needed in the query below that will allow the extraction of records that meet the abovementioned criteria?


SELECT CostPerUnit_Supplies.[_PROV_FULL_NM_CostPerUnit] AS Expr1
, CostPerUnit_Supplies.PROC_CODE_CostPerUnit AS Expr2
, CostPerUnit_Supplies.PROC_DESC_CostPerUnit AS Expr3
, CostPerUnit_Supplies.[Supplies-Jan2007_CostPerUnit] AS Expr4
, CostPerUnit_Supplies.[Supplies-Feb2007_CostPerUnit] AS Expr5
, CostPerUnit_Supplies.[Supplies-Mar2007_CostPerUnit] AS Expr6
, CostPerUnit_Supplies.[Supplies-Jan2008_CostPerUnit] AS Expr7
, CostPerUnit_Supplies.[Supplies-Feb2008_CostPerUnit] AS Expr8
, CostPerUnit_Supplies.[Supplies-Mar2008_CostPerUnit] AS Expr9
FROM CostPerUnit_Supplies
WHERE ((([CostPerUnit_Supplies].[Supplies-Feb2007_CostPerUnit])<>"Supplies-Jan2007_CostPerUnit")
AND (([CostPerUnit_Supplies].[Supplies-Mar2007_CostPerUnit])<>"Supplies-Jan2007_CostPerUnit"))
OR ((([CostPerUnit_Supplies].[Supplies-Feb2007_CostPerUnit])<>"Supplies-Mar2007_CostPerUnit")
AND (([CostPerUnit_Supplies].[Supplies-Mar2007_CostPerUnit])<>"Supplies-Feb2007_CostPerUnit"));

 

[BigRed1212]

I would guess that normalized data in which you had two tables:

ProdTab:
ProductCode
ProductDescription

CostPerUnit:
ProductCode
Month
CostPerUnit

would make this easier, but I will wait for somebody to work some magic and show the other way.

 

[IngDep]

Additional thoughts - maybe, there is a way to check to see if a field is blank or null and then also check to see if any populated fields are different for any given record within the MS Access table.

 

[BigRed1212]

Were you able to figure this out?

 

[IngDep]

I eventually did this manually.

 

[BigRed1212]

That's too bad. I would have thought one of the whizzes around here would have whipped this one right out.