Summery query question

[Shadow]

I have a problem with a summery query and would be appreciated for any kind
of advice.

My situation:
I have a table(tblStock) containing of three fields : PartNo - Incoming -
Outgoing.
The summery query is grouped by [PartNo] and shows [InComingSum] and
[OutGoingSum] and calculates the [Stock] by [InComingSum]-[OutGoingSum].

The problem:
If a [PartNo] contains some [InComing], but the amount of [OutGoing] is
null, [Stock] shows as null too
Is there any possible way to make [Stock] shows the amount of [InComing]
when [OutGoing] amount is null?

this is the sql I used:
SELECT DISTINCTROW tblStock.PartNo , [InComingSum]-[OutGoingSum] AS
Stock, Sum(tblStock.InComing) AS InComingSum,
Sum(tblStock.OutGoingQuan) AS OutGoingSum FROM tblStock GROUP BY
tblStock.PartNo ;

 

[Graham R Seach]

Shadow,

This should do it:

SELECT PartNo,
Sum(Nz(InComing,0)) - Sum (Nz(OutGoing,0)) As Stock,
Sum(Nz(InComing,0)) As InComingSum,
Sum (Nz(OutGoing,0)) As OutGoingSum
FROM tblStock
GROUP BY PartNo

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Shadow]

Graham R Seach,
Thanks for your help. It works perfectly.

Million thanks from Japan
Ghalamkari

Shadow,

This should do it:

SELECT PartNo,
Sum(Nz(InComing,0)) - Sum (Nz(OutGoing,0)) As Stock,
Sum(Nz(InComing,0)) As InComingSum,
Sum (Nz(OutGoing,0)) As OutGoingSum
FROM tblStock
GROUP BY PartNo

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a problem with a summery query and would be appreciated for any
kind
of advice.

My situation:
I have a table(tblStock) containing of three fields : PartNo - Incoming -
Outgoing.
The summery query is grouped by [PartNo] and shows [InComingSum] and
[OutGoingSum] and calculates the [Stock] by [InComingSum]-[OutGoingSum].

The problem:
If a [PartNo] contains some [InComing], but the amount of [OutGoing] is
null, [Stock] shows as null too
Is there any possible way to make [Stock] shows the amount of [InComing]
when [OutGoing] amount is null?

this is the sql I used:
SELECT DISTINCTROW tblStock.PartNo , [InComingSum]-[OutGoingSum] AS
Stock, Sum(tblStock.InComing) AS InComingSum,
Sum(tblStock.OutGoingQuan) AS OutGoingSum FROM tblStock GROUP BY
tblStock.PartNo ;

 

[Graham R Seach]

do itashimashite


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach,
Thanks for your help. It works perfectly.

Million thanks from Japan
Ghalamkari

Shadow,

This should do it:

SELECT PartNo,
Sum(Nz(InComing,0)) - Sum (Nz(OutGoing,0)) As Stock,
Sum(Nz(InComing,0)) As InComingSum,
Sum (Nz(OutGoing,0)) As OutGoingSum
FROM tblStock
GROUP BY PartNo

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a problem with a summery query and would be appreciated for any
kind
of advice.

My situation:
I have a table(tblStock) containing of three fields : PartNo -
Incoming -
Outgoing.
The summery query is grouped by [PartNo] and shows [InComingSum] and
[OutGoingSum] and calculates the [Stock] by
[InComingSum]-[OutGoingSum].

The problem:
If a [PartNo] contains some [InComing], but the amount of [OutGoing] is
null, [Stock] shows as null too
Is there any possible way to make [Stock] shows the amount of [InComing]
when [OutGoing] amount is null?

this is the sql I used:
SELECT DISTINCTROW tblStock.PartNo , [InComingSum]-[OutGoingSum] AS
Stock, Sum(tblStock.InComing) AS InComingSum,
Sum(tblStock.OutGoingQuan) AS OutGoingSum FROM tblStock GROUP BY
tblStock.PartNo ;