Changing value to 0 if condition exists in another field of record

[Kristibaer]

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

 

[KARL DEWEY]

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])

 

[KARL DEWEY]

Fast fingers ---
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

--
KARL DEWEY
Build a little - Test a little

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
--
KARL DEWEY
Build a little - Test a little

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

 

[Kristibaer]

The problem is that it chagnes my records count from 20,783 to 382. What fo
Field1 and Field@ represent in your equation?

Fast fingers ---
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

--
KARL DEWEY
Build a little - Test a little

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
--
KARL DEWEY
Build a little - Test a little

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

 

[KARL DEWEY]

You said '...sum of two fields that will default the sum result to zero if
the item type
field is non-stocking?'

IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) is checking if
stocking and if not adding [Field1]+[Field2] but I did not see where you
were adding two fields in your SQL.


--
KARL DEWEY
Build a little - Test a little

The problem is that it chagnes my records count from 20,783 to 382. What fo
Field1 and Field@ represent in your equation?

Fast fingers ---
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

--
KARL DEWEY
Build a little - Test a little

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

 

[Kristibaer]

Sorry, I've been down with the flu the last few days and bouncing between
home and office, I'm a little stupid. Let me start over...

In the column "Freeze Qty", I want the quantities for records that are
stocking=0 to be zero and the quantities where stocking= -1 to remain as is,
but also keep all 20,783 records in the report. The 'sum' was calculated in
another query "Freeze1".

You said '...sum of two fields that will default the sum result to zero if
the item type
field is non-stocking?'

IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) is checking if
stocking and if not adding [Field1]+[Field2] but I did not see where you
were adding two fields in your SQL.


--
KARL DEWEY
Build a little - Test a little

The problem is that it chagnes my records count from 20,783 to 382. What fo
Field1 and Field@ represent in your equation?

Fast fingers ---
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

--
KARL DEWEY
Build a little - Test a little


:

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

 

[Kristibaer]

Karl: Thanks for your help and patience today.

Played with it a bit more. Seems to have finally worked with the following:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], IIf([immaster].[stocking]=0,[Freeze Qty],0) AS Freeze,
Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze
Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item;

Thanks again!. I'm probably heading home and to bed soon-I'm fading fast.

Have a great weekend,
Kristi

You said '...sum of two fields that will default the sum result to zero if
the item type
field is non-stocking?'

IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) is checking if
stocking and if not adding [Field1]+[Field2] but I did not see where you
were adding two fields in your SQL.


--
KARL DEWEY
Build a little - Test a little

The problem is that it chagnes my records count from 20,783 to 382. What fo
Field1 and Field@ represent in your equation?

Fast fingers ---
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

--
KARL DEWEY
Build a little - Test a little


:

You lost me but here is a guess --
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a field with a calculation that is a sum of two other
fields. I also have another field that detirmines the type of item within a
record, specifically stocking(displays as -1) and non-stocking (displays as
0).

Is there an expression I can use in the criteria of the field that is the
sum of two fields that will default the sum result to zero if the item type
field is non-stocking? Here is the SQL statement if this helps explain what
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi