Converting Nulls to 0 in order to be taken into account in an aggregate

[bifteki via AccessMonster.com]

I am working on a project in Access connected to a db on SQL Server.
I want to make sort of a system for inventory management. I already have a
Companies table (tbl_Companies), as well as a Products table
(tbl_software_products). I also created a product transfer table
(tbl_product_transfers). Each record of this last table is either a delivery
of some product in a certain amount (fld_quantity) from our vendor to us or
to a warehouse where we are renting space. Tbl_product_transfers is related
to fld_company_id (the primary key of tbl_Companies) through both fld_from
and fld_to. Tbl_Companies has over 2000 companies but only 3-4 of these will
be included in tbl_product_transfers (my company, the warehouse and a couple
of vendors).

I want to make a query which, for a specific product, will return one row for
each company that participates with at least one product transfer as a fld_to
company, calculating the sum of the current amount of the product. This way
we will see for example that there are 3 pieces of product A in our offices
and 4 pieces in the warehouse. I have made the following query:

SELECT DISTINCT
dbo.tbl_Companies.fld_company_name, dbo.
tbl_soft_products.fld_software_subcategory, dbo.tbl_Companies.fld_company_id,

dbo.tbl_soft_products.fld_software_id,
(SELECT SUM(fld_quantity)
FROM tbl_product_transfers
WHERE fld_to = dbo.tbl_Companies.
fld_company_id AND fld_product_id = 1141) -
(SELECT SUM(fld_quantity)
FROM tbl_product_transfers
WHERE fld_from = dbo.tbl_Companies.
fld_company_id AND fld_product_id = 1141) AS sum_aarch
FROM dbo.tbl_Companies INNER JOIN
dbo.tbl_product_transfers ON dbo.tbl_Companies.
fld_company_id = dbo.tbl_product_transfers.fld_from INNER JOIN
dbo.tbl_soft_products ON dbo.tbl_product_transfers.
fld_product_id = dbo.tbl_soft_products.fld_software_id
WHERE (dbo.tbl_Companies.fld_company_id IN
(SELECT fld_to
FROM dbo.tbl_product_transfers)) AND
(dbo.tbl_soft_products.fld_software_id = 1141)

Problem is, it works fine if for one company (eg our company) there are
records with it as both a fld_from and a fld_to company, but if a company (eg
the warehouse) has only received but not sent any products it is not included
in the outcome.
I guess the problem occurs in these lines:
(SELECT SUM(fld_quantity)
FROM tbl_product_transfers
WHERE fld_from = dbo.tbl_Companies.
fld_company_id AND fld_product_id = 1141)
As the outcome of these lines is null, it doesn't calculate it in the
subtraction and it just skips it.

My question is, is there any way i can turn null into 0, so it can take part
in the subtraction or is there any workaround for this?

Thanks (and sorry for the long thread).

 

[Sylvain Lafontaine]

Yes, you are right, if there is no line from the subquery, no line are
returned and therefore, the result from the subquery is Null and not 0.
This can be easily corrected by using the IsNull() function to convert a
null result into 0. However, because the first parameter is a subquery, you
must not forget to put the pair of parenthesis around it:

- IsNull ( (Select Sum()...), 0) ) ...

Finally, your design of using relationships for describing an attribut looks
suspicious to me. Instead of using fld_to and fld_from, you should have
only a single fld_Company and use a positive or negative number for the
quantities in order to designate a delivery or a reception. Part of your
problem come from the fact that you are using relationships to describe
other things than a relationship. This is why you are obligated to use a
DISTINCT statement combined with a subquery instead of using a proper Group
By with the Sum directly on the main query. If you don't correct it now,
it's very likely that this design problem will only become worse over time.

Using two differents names like tbl_product_transfers.[fld_product_id] =
tbl_soft_products.[fld_software_id] for describing the same value looks also
very suspicious to me. Finally, get rid of these prefixes tlb_ and fld_ and
adopt the camel notation instead of using all these "_" . It's easier to
the eye to read softwareId, SoftwareId or SoftwareID than software_id. It's
a very daunting task to read a complex query containing hundreds of "_".

Finally, you shoud use aliases instead of repeating the name of your tables.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[bifteki via AccessMonster.com]

I tried the isnull function, even on the sum function (isnull(sum(...)),0)
but still didn't work.
The problem with having only one relation to tbl_companies -ie only one
company_id field- is that the amount of products sent or received from or by
the company, also needs to be added or subtracted to or from another company.
By not having a second field, this information is not stored in the database
in any way.

Another solution I have in mind is to keep just one company field and for
each transfer to store 2 records in the db, one with a positive quantity and
one with a negative one.
eg if company A sends 5 pieces of a product to company B, the first record
would have the company id of compny A with a quantity of -5 and the second
record would have company's B id with a quantity of 5.
But this would create the double number of records.

PS1: You are right that it's not correct to have 2 different names for the
same field (fld_product_id and fld_software_id) but the fld_software_products
table has been there from when we sold only software but now we have other
products too. Problem is, this is a small part of this older access project,
in which there are many references of the table inside the vba code. So, by
renaming it I would have to replace every reference of it and its fields
inside the code. I just named the field this way and thought I'd look later
into the matter of the replacing.
PS2: The fld_... and tbl_... names is the naming used throughout the rest of
the db, so I use it for uniformity reasons.

Yes, you are right, if there is no line from the subquery, no line are
returned and therefore, the result from the subquery is Null and not 0.
This can be easily corrected by using the IsNull() function to convert a
null result into 0. However, because the first parameter is a subquery, you
must not forget to put the pair of parenthesis around it:

- IsNull ( (Select Sum()...), 0) ) ...

Finally, your design of using relationships for describing an attribut looks
suspicious to me. Instead of using fld_to and fld_from, you should have
only a single fld_Company and use a positive or negative number for the
quantities in order to designate a delivery or a reception. Part of your
problem come from the fact that you are using relationships to describe
other things than a relationship. This is why you are obligated to use a
DISTINCT statement combined with a subquery instead of using a proper Group
By with the Sum directly on the main query. If you don't correct it now,
it's very likely that this design problem will only become worse over time.

Using two differents names like tbl_product_transfers.[fld_product_id] =
tbl_soft_products.[fld_software_id] for describing the same value looks also
very suspicious to me. Finally, get rid of these prefixes tlb_ and fld_ and
adopt the camel notation instead of using all these "_" . It's easier to
the eye to read softwareId, SoftwareId or SoftwareID than software_id. It's
a very daunting task to read a complex query containing hundreds of "_".

Finally, you shoud use aliases instead of repeating the name of your tables.

I am working on a project in Access connected to a db on SQL Server.
I want to make sort of a system for inventory management. I already have a

[quoted text clipped - 67 lines]

Thanks (and sorry for the long thread).

 

[Sylvain Lafontaine]

I tried the isnull function, even on the sum function (isnull(sum(...)),0)
but still didn't work.

You must put the IsNull() not around the sum function but around the
subquery, without forgetting to put the parenthesis around the subquery
itself:

select 1 as a, (select 2 where 1=0) as b;
select 1 as a, IsNull ((select 2 where 1=0), 0) as b;

The problem with having only one relation to tbl_companies -ie only one
company_id field- is that the amount of products sent or received from or
by
the company, also needs to be added or subtracted to or from another
company.
By not having a second field, this information is not stored in the
database
in any way.

Another solution I have in mind is to keep just one company field and for
each transfer to store 2 records in the db, one with a positive quantity
and
one with a negative one.
eg if company A sends 5 pieces of a product to company B, the first record
would have the company id of compny A with a quantity of -5 and the second
record would have company's B id with a quantity of 5.
But this would create the double number of records.

Well, if you always have two companies associated for the same transfer,
that the amount is always equal between these two companies and that there
is no difference of information whatsoever then yes, it's a valid design.
The first thing that come to mind is that there would be many different
piece of informations between these two processes, like the time of
reception or of expedition, reception or expedition certificates, multiple
steps; for example, a reception split into multiple expeditions - possibly
toward different companies or vice-versa, etc. The fact that you can have a
reception record without a matching expedition record is a clear indication
to that.

Also, the information stored into the database is just one part of the
equation. The other part is how you deal with it to build your queries and
retrieve your results. With a single relationship and two records, the
queries are simple; with two relationships and a single record, they are
much more complex and you get some strange border effect like having to deal
with records which can have or not a matching expedition company inside the
same record.

There is also the problem that when a record has been created and filled for
a reception; you must later change the same record to include the expedition
information. This is clearly a violation of one of the basic rules of a
good database design which says that once a record has been created and
filed, you shouldn't have to change its content later. The only changes
that should be done to existing informations are those who don't
fundamentally change the information already recorded. For example, if a
person owes you 1000$, then you don't change the record that has this
information stored. What you will do would be to add a new record with the
new balance if this person reimburses you but you won't change the existing
record with an amount due of 1000$.

However, you can change the name of a person in the database - possibly with
a log entry somewhere about the change - because this doesn't fundamentally
change the recorded information: this is still the same person and this
person still owes 1000$ after the renaming.

This should be the same thing with your database: you add new records to it
to store new informations when they occurds; you don't change existing
records.

PS1: You are right that it's not correct to have 2 different names for the
same field (fld_product_id and fld_software_id) but the
fld_software_products
table has been there from when we sold only software but now we have other
products too. Problem is, this is a small part of this older access
project,
in which there are many references of the table inside the vba code. So,
by
renaming it I would have to replace every reference of it and its fields
inside the code. I just named the field this way and thought I'd look
later
into the matter of the replacing.
PS2: The fld_... and tbl_... names is the naming used throughout the rest
of
the db, so I use it for uniformity reasons.

Yes, you are right, if there is no line from the subquery, no line are
returned and therefore, the result from the subquery is Null and not 0.
This can be easily corrected by using the IsNull() function to convert a
null result into 0. However, because the first parameter is a subquery,
you
must not forget to put the pair of parenthesis around it:

- IsNull ( (Select Sum()...), 0) ) ...

Finally, your design of using relationships for describing an attribut
looks
suspicious to me. Instead of using fld_to and fld_from, you should have
only a single fld_Company and use a positive or negative number for the
quantities in order to designate a delivery or a reception. Part of your
problem come from the fact that you are using relationships to describe
other things than a relationship. This is why you are obligated to use a
DISTINCT statement combined with a subquery instead of using a proper
Group
By with the Sum directly on the main query. If you don't correct it now,
it's very likely that this design problem will only become worse over
time.

Using two differents names like tbl_product_transfers.[fld_product_id] =
tbl_soft_products.[fld_software_id] for describing the same value looks
also
very suspicious to me. Finally, get rid of these prefixes tlb_ and fld_
and
adopt the camel notation instead of using all these "_" . It's easier to
the eye to read softwareId, SoftwareId or SoftwareID than software_id.
It's
a very daunting task to read a complex query containing hundreds of "_".

Finally, you shoud use aliases instead of repeating the name of your
tables.

I am working on a project in Access connected to a db on SQL Server.
I want to make sort of a system for inventory management. I already have
a

[quoted text clipped - 67 lines]

Thanks (and sorry for the long thread).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Forgot to mention that the other possibility would be to replace the two
subqueries with a join on both field fld_to and fld_from and add a Case
statement inside the Sum() to negate the value when necessary.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

I tried the isnull function, even on the sum function (isnull(sum(...)),0)
but still didn't work.

You must put the IsNull() not around the sum function but around the
subquery, without forgetting to put the parenthesis around the subquery
itself:

select 1 as a, (select 2 where 1=0) as b;
select 1 as a, IsNull ((select 2 where 1=0), 0) as b;

The problem with having only one relation to tbl_companies -ie only one
company_id field- is that the amount of products sent or received from or
by
the company, also needs to be added or subtracted to or from another
company.
By not having a second field, this information is not stored in the
database
in any way.

Another solution I have in mind is to keep just one company field and for
each transfer to store 2 records in the db, one with a positive quantity
and
one with a negative one.
eg if company A sends 5 pieces of a product to company B, the first
record
would have the company id of compny A with a quantity of -5 and the
second
record would have company's B id with a quantity of 5.
But this would create the double number of records.

Well, if you always have two companies associated for the same transfer,
that the amount is always equal between these two companies and that there
is no difference of information whatsoever then yes, it's a valid design.
The first thing that come to mind is that there would be many different
piece of informations between these two processes, like the time of
reception or of expedition, reception or expedition certificates, multiple
steps; for example, a reception split into multiple expeditions - possibly
toward different companies or vice-versa, etc. The fact that you can have
a reception record without a matching expedition record is a clear
indication to that.

Also, the information stored into the database is just one part of the
equation. The other part is how you deal with it to build your queries
and retrieve your results. With a single relationship and two records,
the queries are simple; with two relationships and a single record, they
are much more complex and you get some strange border effect like having
to deal with records which can have or not a matching expedition company
inside the same record.

There is also the problem that when a record has been created and filled
for a reception; you must later change the same record to include the
expedition information. This is clearly a violation of one of the basic
rules of a good database design which says that once a record has been
created and filed, you shouldn't have to change its content later. The
only changes that should be done to existing informations are those who
don't fundamentally change the information already recorded. For example,
if a person owes you 1000$, then you don't change the record that has this
information stored. What you will do would be to add a new record with
the new balance if this person reimburses you but you won't change the
existing record with an amount due of 1000$.

However, you can change the name of a person in the database - possibly
with a log entry somewhere about the change - because this doesn't
fundamentally change the recorded information: this is still the same
person and this person still owes 1000$ after the renaming.

This should be the same thing with your database: you add new records to
it to store new informations when they occurds; you don't change existing
records.

PS1: You are right that it's not correct to have 2 different names for
the
same field (fld_product_id and fld_software_id) but the
fld_software_products
table has been there from when we sold only software but now we have
other
products too. Problem is, this is a small part of this older access
project,
in which there are many references of the table inside the vba code. So,
by
renaming it I would have to replace every reference of it and its fields
inside the code. I just named the field this way and thought I'd look
later
into the matter of the replacing.
PS2: The fld_... and tbl_... names is the naming used throughout the rest
of
the db, so I use it for uniformity reasons.

Yes, you are right, if there is no line from the subquery, no line are
returned and therefore, the result from the subquery is Null and not 0.
This can be easily corrected by using the IsNull() function to convert a
null result into 0. However, because the first parameter is a subquery,
you
must not forget to put the pair of parenthesis around it:

- IsNull ( (Select Sum()...), 0) ) ...

Finally, your design of using relationships for describing an attribut
looks
suspicious to me. Instead of using fld_to and fld_from, you should have
only a single fld_Company and use a positive or negative number for the
quantities in order to designate a delivery or a reception. Part of your
problem come from the fact that you are using relationships to describe
other things than a relationship. This is why you are obligated to use a
DISTINCT statement combined with a subquery instead of using a proper
Group
By with the Sum directly on the main query. If you don't correct it now,
it's very likely that this design problem will only become worse over
time.

Using two differents names like tbl_product_transfers.[fld_product_id] =
tbl_soft_products.[fld_software_id] for describing the same value looks
also
very suspicious to me. Finally, get rid of these prefixes tlb_ and fld_
and
adopt the camel notation instead of using all these "_" . It's easier to
the eye to read softwareId, SoftwareId or SoftwareID than software_id.
It's
a very daunting task to read a complex query containing hundreds of "_".

Finally, you shoud use aliases instead of repeating the name of your
tables.

I am working on a project in Access connected to a db on SQL Server.
I want to make sort of a system for inventory management. I already
have a
[quoted text clipped - 67 lines]

Thanks (and sorry for the long thread).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[bifteki via AccessMonster.com]

I tried the isnull() function around the subquery too but it didn't work.
Maybe it wasn't a problem of NULL after all.

About this part of your reply:
"There is also the problem that when a record has been created and filled for
a reception; you must later change the same record to include the expedition
information. This is clearly a violation of one of the basic rules of a
good database design which says that once a record has been created and
filed, you shouldn't have to change its content later."
The idea was to Insert one record into the table for each shipment of one
kind of product. This would be done through a form where the user would
choose both the shipping and the receiving company. So, all information of
the record would be inserted at once. If the user later wanted to send some
products from the company who had received them to another one, this would
mean a new filling in of the fields in the form and another Insert into the
table.

I guess I'll use the simplified design, with 2 records per transfer.

There's just one thing I didn't quite understand however.
"The first thing that come to mind is that there would be many different
piece of informations between these two processes, like the time of
reception or of expedition, reception or expedition certificates, multiple
steps; for example, a reception split into multiple expeditions - possibly
toward different companies or vice-versa, etc. The fact that you can have a
reception record without a matching expedition record is a clear indication
to that."
Are you referring to my original design, with the fld_from and fld_to fields?
As I can understand, you are saying that there's much information that refers
to either the receiving or the shipment of the products, not to both, thus it
would be better to have one record for each?

Forgot to mention that the other possibility would be to replace the two
subqueries with a join on both field fld_to and fld_from and add a Case
statement inside the Sum() to negate the value when necessary.

I tried the isnull function, even on the sum function (isnull(sum(...)),0)
but still didn't work. [quoted text clipped - 125 lines]

Thanks (and sorry for the long thread).

 

[Sylvain Lafontaine]

I tried the isnull() function around the subquery too but it didn't work.
Maybe it wasn't a problem of NULL after all.

Well, it should be pretty easy to determine if it's a Null problem or not by
displaying its value into another column. Maybe it's not a problem related
to Null at all but as you didn't provide any sample data or a copy of what
you actually tried for real, it's impossible for me to say.

About this part of your reply:
"There is also the problem that when a record has been created and filled
for
a reception; you must later change the same record to include the
expedition
information. This is clearly a violation of one of the basic rules of a
good database design which says that once a record has been created and
filed, you shouldn't have to change its content later."
The idea was to Insert one record into the table for each shipment of one
kind of product. This would be done through a form where the user would
choose both the shipping and the receiving company. So, all information of
the record would be inserted at once. If the user later wanted to send
some
products from the company who had received them to another one, this would
mean a new filling in of the fields in the form and another Insert into
the
table.

I guess I'll use the simplified design, with 2 records per transfer.

There's just one thing I didn't quite understand however.
"The first thing that come to mind is that there would be many different
piece of informations between these two processes, like the time of
reception or of expedition, reception or expedition certificates, multiple
steps; for example, a reception split into multiple expeditions - possibly
toward different companies or vice-versa, etc. The fact that you can have
a
reception record without a matching expedition record is a clear
indication
to that."
Are you referring to my original design, with the fld_from and fld_to
fields?
As I can understand, you are saying that there's much information that
refers
to either the receiving or the shipment of the products, not to both, thus
it
would be better to have one record for each?

I'm not saying that there's much information different between these two
processes for the simple reason that I'm not sitting at the front of your
desk and therefore, I know nothing about the real situation at your company.
This tentative explanation was based on my vague expectation of what could
be such a situation and therefore, could be partially or even 100% totally
out of track. t's up to you to take whatever you want from it - if you want
any part of it - and transform it into whatever form or usage you like.

I cannot even say thas this would be what I would have done if I was at your
place.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[bifteki via AccessMonster.com]

I have already isolated the second subquery, which I guess had the problem
and ran it as a seperate query with the Isnull function. It worked fine
returning 0 in place of Null. So it was indeed not a Null problem.

Anyway, I'll try this simplified way with the 2 records. What's important is
for it to work and this will make it easier for me to make it work.

Thank you very much for your help.

I tried the isnull() function around the subquery too but it didn't work.
Maybe it wasn't a problem of NULL after all.

Well, it should be pretty easy to determine if it's a Null problem or not by
displaying its value into another column. Maybe it's not a problem related
to Null at all but as you didn't provide any sample data or a copy of what
you actually tried for real, it's impossible for me to say.

About this part of your reply:
"There is also the problem that when a record has been created and filled

[quoted text clipped - 33 lines]

it
would be better to have one record for each?

I'm not saying that there's much information different between these two
processes for the simple reason that I'm not sitting at the front of your
desk and therefore, I know nothing about the real situation at your company.
This tentative explanation was based on my vague expectation of what could
be such a situation and therefore, could be partially or even 100% totally
out of track. t's up to you to take whatever you want from it - if you want
any part of it - and transform it into whatever form or usage you like.

I cannot even say thas this would be what I would have done if I was at your
place.

 

[Sylvain Lafontaine]

Then maybe it's just a question of using a Left Join instead of an Inner
Join in your query. However, as you didn't provide any sample data along
with the expected result and the full schema (design)

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

I have already isolated the second subquery, which I guess had the problem
and ran it as a seperate query with the Isnull function. It worked fine
returning 0 in place of Null. So it was indeed not a Null problem.

Anyway, I'll try this simplified way with the 2 records. What's important
is
for it to work and this will make it easier for me to make it work.

Thank you very much for your help.

I tried the isnull() function around the subquery too but it didn't work.
Maybe it wasn't a problem of NULL after all.

Well, it should be pretty easy to determine if it's a Null problem or not
by
displaying its value into another column. Maybe it's not a problem
related
to Null at all but as you didn't provide any sample data or a copy of what
you actually tried for real, it's impossible for me to say.

About this part of your reply:
"There is also the problem that when a record has been created and
filled

[quoted text clipped - 33 lines]

it
would be better to have one record for each?

I'm not saying that there's much information different between these two
processes for the simple reason that I'm not sitting at the front of your
desk and therefore, I know nothing about the real situation at your
company.
This tentative explanation was based on my vague expectation of what could
be such a situation and therefore, could be partially or even 100% totally
out of track. t's up to you to take whatever you want from it - if you
want
any part of it - and transform it into whatever form or usage you like.

I cannot even say thas this would be what I would have done if I was at
your
place.

 

[Sylvain Lafontaine]

Sorry, wrong button; restarting:

Then maybe it's just a question of using a Left Join instead of an Inner
Join in your main query. However, as you didn't provide any sample data
along with the expected result and the details of your tables; it's hard for
me to even just understand what your design really is; much less to provide
any solution for certainty.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

I have already isolated the second subquery, which I guess had the problem
and ran it as a seperate query with the Isnull function. It worked fine
returning 0 in place of Null. So it was indeed not a Null problem.

Anyway, I'll try this simplified way with the 2 records. What's important
is
for it to work and this will make it easier for me to make it work.

Thank you very much for your help.

I tried the isnull() function around the subquery too but it didn't work.
Maybe it wasn't a problem of NULL after all.

Well, it should be pretty easy to determine if it's a Null problem or not
by
displaying its value into another column. Maybe it's not a problem
related
to Null at all but as you didn't provide any sample data or a copy of what
you actually tried for real, it's impossible for me to say.

About this part of your reply:
"There is also the problem that when a record has been created and
filled

[quoted text clipped - 33 lines]

it
would be better to have one record for each?

I'm not saying that there's much information different between these two
processes for the simple reason that I'm not sitting at the front of your
desk and therefore, I know nothing about the real situation at your
company.
This tentative explanation was based on my vague expectation of what could
be such a situation and therefore, could be partially or even 100% totally
out of track. t's up to you to take whatever you want from it - if you
want
any part of it - and transform it into whatever form or usage you like.

I cannot even say thas this would be what I would have done if I was at
your
place.

 

[bifteki via AccessMonster.com]

Anyway I already did it the two-records way. My boss didn't think it was
redundant, he just said "whatever works", so it's fine.

Thank you again for your help.

Sorry, wrong button; restarting:

Then maybe it's just a question of using a Left Join instead of an Inner
Join in your main query. However, as you didn't provide any sample data
along with the expected result and the details of your tables; it's hard for
me to even just understand what your design really is; much less to provide
any solution for certainty.

I have already isolated the second subquery, which I guess had the problem
and ran it as a seperate query with the Isnull function. It worked fine

[quoted text clipped - 36 lines]