Problem Using "LEFT JOIN" and Mathematical Operations

  • Thread starter Thread starter RocketDude
  • Start date Start date
R

RocketDude

Hi,

I'm having a problem combining the "LEFT JOIN" SQL method and mathematical
operations (in this case subtraction). My problem is that I am trying to
subtract monthly sales data from two tables, where my left table has some
data that my right table does not, and wherever the data is missing from the
right table, Access sets the resulting subtraction to "Blank". Where it
should be just the value in the left table.

For example, assume I have two tables Company, DivisionA, and I want to find
out what the monthly sales by product are for the Company, not including the
monthly sales for DivisionA (In essences I want to see what Company -
DivisionA is by product by month). And further assume that the Company table
contains some products (rows) that are not in the DivisionA table. Right
now, my SQL looks like:

SELECT Company.Distributor, Company.Product, ([Company]![Jan02] -
[DivisionA]![Jan02]) AS Jan02
INTO TableA
FROM Company LEFT JOIN Division ON (Company.Distributor =
DivisionA.Distributor) AND (Company.Product = DivisionA.Product)

How do I get Access to set "([Company]![Jan02] - [DivisionA]![Jan02]) " =
"([Company]![Jan02] " when " [DivisionA]![Jan02]" doesn't exist?

TIA,
 
Under these circumstances, DivisionA.Jan02 will be Null, and 'Nulls
propagate', meaning that as a general rule if any part of an expression
evaluates to Null, the entire expression will evaluate to Null. This is
because Null represents an unknown value. What is the result of subtracting
an unknown quantity from a known quantity? The answer is, of course, an
unknown quantity, therefore, Null.

If you want to change this default behaviour, you need to substitute
something else for the Null value. When working within Microsoft Access, the
easiest way to do this is using the NZ function. (Just remember that this is
an Access function, so you can't use it outside of Access. If you want to
use a Jet query outside of Access, use the IIF function instead of NZ).

Using NZ, your expression would look something like this ...

([Company].[Jan02] - NZ([DivisionA].[Jan02], 0)) AS Jan02

See 'NZ Function' in the help files for details.
 
Brendan,

Thank you for the info re: Null behavior and the NZ function. I ended up
fixing it using the IIF function and checking to see if DivisionA.Jan02 is
null before performing the subtraction.

Regards,

--
Matthew


Brendan Reynolds said:
Under these circumstances, DivisionA.Jan02 will be Null, and 'Nulls
propagate', meaning that as a general rule if any part of an expression
evaluates to Null, the entire expression will evaluate to Null. This is
because Null represents an unknown value. What is the result of
subtracting an unknown quantity from a known quantity? The answer is, of
course, an unknown quantity, therefore, Null.

If you want to change this default behaviour, you need to substitute
something else for the Null value. When working within Microsoft Access,
the easiest way to do this is using the NZ function. (Just remember that
this is an Access function, so you can't use it outside of Access. If you
want to use a Jet query outside of Access, use the IIF function instead of
NZ).

Using NZ, your expression would look something like this ...

([Company].[Jan02] - NZ([DivisionA].[Jan02], 0)) AS Jan02

See 'NZ Function' in the help files for details.

--
Brendan Reynolds (MVP)

RocketDude said:
Hi,

I'm having a problem combining the "LEFT JOIN" SQL method and
mathematical operations (in this case subtraction). My problem is that I
am trying to subtract monthly sales data from two tables, where my left
table has some data that my right table does not, and wherever the data
is missing from the right table, Access sets the resulting subtraction to
"Blank". Where it should be just the value in the left table.

For example, assume I have two tables Company, DivisionA, and I want to
find out what the monthly sales by product are for the Company, not
including the monthly sales for DivisionA (In essences I want to see what
Company - DivisionA is by product by month). And further assume that the
Company table contains some products (rows) that are not in the DivisionA
table. Right now, my SQL looks like:

SELECT Company.Distributor, Company.Product, ([Company]![Jan02] -
[DivisionA]![Jan02]) AS Jan02
INTO TableA
FROM Company LEFT JOIN Division ON (Company.Distributor =
DivisionA.Distributor) AND (Company.Product = DivisionA.Product)

How do I get Access to set "([Company]![Jan02] - [DivisionA]![Jan02]) " =
"([Company]![Jan02] " when " [DivisionA]![Jan02]" doesn't exist?

TIA,
Click to expand...
Click to expand...
 
Back
Top