Change value of field to 0 if specific condition exists on other f

K

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).

Have tried variations of IIF([immaster].[stocking] = 0, 0,
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes from
20,783 to 382 or I get no results.

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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty]
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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi
 
K

KARL DEWEY

It does not go in criteria!
What two fields are you wanting to add? The SQL does not show the addition
of any fields. I also do not see any fields being summed.
 
K

Kristibaer

I have actully used the Iif function as criteria bafore and it worked fine,
but I did try it in a separate column as well. Like I said, I'm not all here
today.

KARL DEWEY said:
It does not go in criteria!
What two fields are you wanting to add? The SQL does not show the addition
of any fields. I also do not see any fields being summed.


--
KARL DEWEY
Build a little - Test a little


Kristibaer said:
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).

Have tried variations of IIF([immaster].[stocking] = 0, 0,
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes from
20,783 to 382 or I get no results.

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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty]
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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi
Click to expand...
Click to expand...
 
C

Conan Kelly

Kristibaer,

Like Karl mentioned, I don't think you want to use IIF in the criteria.

Let me ask, you want all records in your table to display in your query,
correct? But you want anything that is non-stocking to display a 0, and
anything that is stocking to display the sum of 2 other fields? Is that
correct?

Try this for your select clause:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty], IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) as
[Enter your desired new field name here]

That should create a new calculated field in your query that will do what
you want. This new field will be called "Enter your desired new field name
here". Be sure to change that appropriatly ;-).

Also, I think you will need to put the IIF part of it (I don't think you
need the "as [FieldName]" part) in the GROUP BY clause like so:

GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]),
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]);

HTH,

Conan
 
K

Kristibaer

As I explained to Karl earlier, I've been down with the flu the last few days
and bouncing between home and work; I'm a little stupid today. In the column
"Freeze Qty", I want the quantities for records where 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).

Below I have one item where stocking=0 and one where stocking= -1. For
ACM10417, I want the Freeze Qty value to be changed to 0.


item stocking Freeze Qty Count Count Qty Variance Qty
ACM10417 0 1 0 -1
ADFSPRG -1 1 1 1 0


Conan Kelly said:
Kristibaer,

Like Karl mentioned, I don't think you want to use IIF in the criteria.

Let me ask, you want all records in your table to display in your query,
correct? But you want anything that is non-stocking to display a 0, and
anything that is stocking to display the sum of 2 other fields? Is that
correct?

Try this for your select clause:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty], IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) as
[Enter your desired new field name here]

That should create a new calculated field in your query that will do what
you want. This new field will be called "Enter your desired new field name
here". Be sure to change that appropriatly ;-).

Also, I think you will need to put the IIF part of it (I don't think you
need the "as [FieldName]" part) in the GROUP BY clause like so:

GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]),
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]);

HTH,

Conan






Kristibaer said:
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).

Have tried variations of IIF([immaster].[stocking] = 0, 0,
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes from
20,783 to 382 or I get no results.

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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty]
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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi
Click to expand...
Click to expand...
 
C

Conan Kelly

Kristibaer,

Using the SQL statement you posted earlier, will this work for you?:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI
Freeze].prodclas,immaster.stocking, iif(immaster.stocking=0,0, [PI
Freeze].[Freeze Qty]) as [Freeze Qty], [MAST CNT].Count,
Nz([MASTCNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze
Qty]) AS [Variance Qty]
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,immaster.stocking, iif(immaster.stocking=0,0, [PI
Freeze].[Freeze Qty]), [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count
Qty]-[PI Freeze].[Freeze Qty]);

If you notice, in the SQL statement above, I replaced your "[PI
Freeze].[Freeze Qty]" field with an IIF() function that has [PI
Freeze].[Freeze Qty] in the "FALSE" argument ofthe IIF() function.

If, for some reason Access doesn't like you giving an alias to a calculated
column that is the name of an existing column ("as [Freeze Qty]"), change
"as [Freeze Qty]" to something like "as [New Freeze Qty]". But I don't
think Access will have a problem with that.

HTH,

Conan





Kristibaer said:
As I explained to Karl earlier, I've been down with the flu the last few
days
and bouncing between home and work; I'm a little stupid today. In the
column
"Freeze Qty", I want the quantities for records where 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).

Below I have one item where stocking=0 and one where stocking= -1. For
ACM10417, I want the Freeze Qty value to be changed to 0.


item stocking Freeze Qty Count Count Qty Variance Qty
ACM10417 0 1 0 -1
ADFSPRG -1 1 1 1 0


Conan Kelly said:
Kristibaer,

Like Karl mentioned, I don't think you want to use IIF in the criteria.

Let me ask, you want all records in your table to display in your query,
correct? But you want anything that is non-stocking to display a 0, and
anything that is stocking to display the sum of 2 other fields? Is that
correct?

Try this for your select clause:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty], IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) as
[Enter your desired new field name here]

That should create a new calculated field in your query that will do what
you want. This new field will be called "Enter your desired new field
name
here". Be sure to change that appropriatly ;-).

Also, I think you will need to put the IIF part of it (I don't think you
need the "as [FieldName]" part) in the GROUP BY clause like so:

GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]),
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]);

HTH,

Conan






Kristibaer said:
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).

Have tried variations of IIF([immaster].[stocking] = 0, 0,
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes
from
20,783 to 382 or I get no results.

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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty])
AS
[Variance Qty]
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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi
Click to expand...
Click to expand...
Click to expand...
 
K

Kristibaer

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

Conan Kelly said:
Kristibaer,

Like Karl mentioned, I don't think you want to use IIF in the criteria.

Let me ask, you want all records in your table to display in your query,
correct? But you want anything that is non-stocking to display a 0, and
anything that is stocking to display the sum of 2 other fields? Is that
correct?

Try this for your select clause:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty], IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]) as
[Enter your desired new field name here]

That should create a new calculated field in your query that will do what
you want. This new field will be called "Enter your desired new field name
here". Be sure to change that appropriatly ;-).

Also, I think you will need to put the IIF part of it (I don't think you
need the "as [FieldName]" part) in the GROUP BY clause like so:

GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]),
IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2]);

HTH,

Conan






Kristibaer said:
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).

Have tried variations of IIF([immaster].[stocking] = 0, 0,
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes from
20,783 to 382 or I get no results.

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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS
[Variance Qty]
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,
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing value to 0 if condition exists in another field of record 6
On duplicate change value of other field 2
PivotItems.Count is 0 when items are visible on the sheet 4
VBA Coding Help - Need to edit field 1
Convert T/F to Number value & Sum multiple fields for further calc 3
Reference field from query to an unbound txt box on form 0
Is there a better way to run a parameter query on a form? 0
If field value has changed allow an audit - if not allow form to c 2

Top