Negative Value in Third and quarter column, how fix ?

[ldiaz]

here is the statements
==================================================
SELECT IDVolatilDBID_lbl.User6 AS Style,
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0))
AS [Sum Of Qty (Rel-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",1,0))
AS [Sum Of Qty (Rel-JAXM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes))
AS [Sum Of Qty (CUT-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes)) AS [Sum Of Qty (CUT-JAXM-06)]
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
WHERE (((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06")) OR
(((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06") AND
((IDVolatilDBID_lbl.Cut_Process_Release)=Yes))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0)) DESC;
==================================================

 

[John W. Vinson]

here is the statements
==================================================
SELECT IDVolatilDBID_lbl.User6 AS Style,
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0))
AS [Sum Of Qty (Rel-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",1,0))
AS [Sum Of Qty (Rel-JAXM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes))
AS [Sum Of Qty (CUT-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes)) AS [Sum Of Qty (CUT-JAXM-06)]
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
WHERE (((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06")) OR
(((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06") AND
((IDVolatilDBID_lbl.Cut_Process_Release)=Yes))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0)) DESC;
==================================================

Just a guess here... you're summing an expression like

([IDVolatilDBID_lbl.Cut_Process_Release])=Yes

That expression is either False - which is stored as a number 0 - or it's
True, which is stored as a negative 1. If you're assuming that each
Cut_Process_Release will add 1 to the sum if it's true, you're going to be
disappointed - it will SUBTRACT 1 instead, since it's -1!

In addition, your last two IIF statements have only two arguments, not three.
What do you want to sum in these cases?

John W. Vinson [MVP]

 

[ldiaz]

I have put -1 instead of 'Yes' but is not fixed


LD
--
Lorenzo Díaz
Cad Technician

here is the statements
==================================================
SELECT IDVolatilDBID_lbl.User6 AS Style,
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0))
AS [Sum Of Qty (Rel-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",1,0))
AS [Sum Of Qty (Rel-JAXM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes))
AS [Sum Of Qty (CUT-BAM-06)],
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes)) AS [Sum Of Qty (CUT-JAXM-06)]
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
WHERE (((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06")) OR
(((IDVolatilDBID_lbl.Planner)="BAM-06" Or
(IDVolatilDBID_lbl.Planner)="JAXM-06") AND
((IDVolatilDBID_lbl.Cut_Process_Release)=Yes))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY
Sum([IDVolatilDBID_lbl].[Qty]*IIf([IDVolatilDBID_lbl].[Planner]="BAM-06",1,0)) DESC;
==================================================

Just a guess here... you're summing an expression like

([IDVolatilDBID_lbl.Cut_Process_Release])=Yes

That expression is either False - which is stored as a number 0 - or it's
True, which is stored as a negative 1. If you're assuming that each
Cut_Process_Release will add 1 to the sum if it's true, you're going to be
disappointed - it will SUBTRACT 1 instead, since it's -1!

In addition, your last two IIF statements have only two arguments, not three.
What do you want to sum in these cases?

John W. Vinson [MVP]

 

[John W. Vinson]

I have put -1 instead of 'Yes' but is not fixed

That's not what I suggested, and no, of course it wouldn't work. The constant
Yes and the constant -1 are simply two ways of depicting the same value.

Could you explain what the expression

IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes))

is *INTENDED* to accomplish? What it's actually doing is:

If [IDVolatilDBID_lbl].[Planner] is equal to "JAXM-06" it's looking at the
value in [IDVolatilDBID_lbl.Cut_Process_Release]. If that value is True (or
-1, or Yes) it's returning -1 (true, yes); if that value is false it's
returning 0.

If [IDVolatilDBID_lbl].[Planner] is anything else it's probably going to
error, or at best return NULL, since you have no third operand for the IIF.

Stop. Step back. Explain what you are trying to *accomplish* with this, rather
than how you're trying to accomplish it!

John W. Vinson [MVP]

 

[ldiaz]

Ok, this is what I want:
Style | A | B | C | D | E | F |
CONC 5 2 2 1 2 1
TOC 5 2 2 1 2 1

A=Sum Of Qty (Rel-BAM-06)
B=Sum Of Qty (Rel-JAXM-06)
C=Sum Of Qty (CUT-BAM-06)
D=Sum Of Qty (CUT-JAXM-06)
E=Sum Of Qty (Sub-BAM-06)
F=Sum Of Qty (Sub-JAXM-06)

IDVolatilDBID_lbl (is the name of table)
[User6] contain styles like CONC,TOC, other
[Planner] contain just two kind of datas,BAM-06 or JAXM-07
[Cut_Process_Release] this is check box that is checked when cut process is
completed
[Sub_Assy_Release] this is check box that is checked when SUB process is
completed
[Qty] is the field where the qty is reflected

could be possible to help me with this?, if needed more info just let me knwo,

Thanks in advance


--
Lorenzo Díaz
Cad Technician

I have put -1 instead of 'Yes' but is not fixed

That's not what I suggested, and no, of course it wouldn't work. The constant
Yes and the constant -1 are simply two ways of depicting the same value.

Could you explain what the expression

IIf([IDVolatilDBID_lbl].[Planner]="JAXM-06",([IDVolatilDBID_lbl.Cut_Process_Release])=Yes))

is *INTENDED* to accomplish? What it's actually doing is:

If [IDVolatilDBID_lbl].[Planner] is equal to "JAXM-06" it's looking at the
value in [IDVolatilDBID_lbl.Cut_Process_Release]. If that value is True (or
-1, or Yes) it's returning -1 (true, yes); if that value is false it's
returning 0.

If [IDVolatilDBID_lbl].[Planner] is anything else it's probably going to
error, or at best return NULL, since you have no third operand for the IIF.

Stop. Step back. Explain what you are trying to *accomplish* with this, rather
than how you're trying to accomplish it!

John W. Vinson [MVP]