Help in Query with two condition

[ldiaz]

hello all,
I have a table like this:
Planner Qty User6 CUT SUB
BAM-06 1 CONC Yes No
BAM-06 1 CONC No Yes
BAM-06 1 TOC Yes No
BAM-06 1 TOC No Yes
BAM-06 1 TACT Yes No
BAM-06 1 TACT No Yes
JAXM-06 1 CONC Yes No
JAXM-06 1 CONC No Yes
JAXM-06 1 TOC Yes No
JAXM-06 1 TOC No Yes
JAXM-06 1 TACT Yes No
JAXM-06 1 TACT No Yes

Note: CUT and SUB are check box

I want a result as follow:
User6 A B C D E F
CONC 2 2 1 1 1 1
TACT 2 2 1 1 1 1
TOC 2 2 1 1 1 1

WHERE:
A SUM Qty =BAM-06
B SUM Qty =JAXM-06
C SUM Qty =BAM-06 if Cut=YES
D SUM Qty =JAXM-06 if Cut=YES
E SUM Qty =BAM-06 if SUB=YES
F SUM Qty =JAXM-06 if SUB=YES

I will replace A,B,C,D,E,F with my titles that I want to show in query,

please help and Thanks in advance

 

[Lance]

use an iif statement like:

iif([CUT] = "Yes",[BAM-06],0)

Then sum that.

 

[Lance]

Use an IIF statement like

iif(Planner = "Bam-06" AND Cut = "Yes",Qty,0)

Then sum that.

 

[Lance]

Ignore this.. see the one below.

use an iif statement like:

iif([CUT] = "Yes",[BAM-06],0)

Then sum that.

hello all,
I have a table like this:
Planner Qty User6 CUT SUB
BAM-06 1 CONC Yes No
BAM-06 1 CONC No Yes
BAM-06 1 TOC Yes No
BAM-06 1 TOC No Yes
BAM-06 1 TACT Yes No
BAM-06 1 TACT No Yes
JAXM-06 1 CONC Yes No
JAXM-06 1 CONC No Yes
JAXM-06 1 TOC Yes No
JAXM-06 1 TOC No Yes
JAXM-06 1 TACT Yes No
JAXM-06 1 TACT No Yes

Note: CUT and SUB are check box

I want a result as follow:
User6 A B C D E F
CONC 2 2 1 1 1 1
TACT 2 2 1 1 1 1
TOC 2 2 1 1 1 1

WHERE:
A SUM Qty =BAM-06
B SUM Qty =JAXM-06
C SUM Qty =BAM-06 if Cut=YES
D SUM Qty =JAXM-06 if Cut=YES
E SUM Qty =BAM-06 if SUB=YES
F SUM Qty =JAXM-06 if SUB=YES

I will replace A,B,C,D,E,F with my titles that I want to show in query,

please help and Thanks in advance

 

[ldiaz]

Hi Lance, I have built my query with your help., now it working


here is:
==========================================
SELECT IDVolatilDBID_lbl.User6 AS Style,
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="BAM-06",1,0)) AS
[Rel-BAM-06],
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="JAXM-06",1,0)) AS
[Rel-JAXM-06],
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="BAM-06" And
IDVolatilDBID_lbl.Cut_Process_Release=Yes,1,0)) AS [CUT-BAM-06],
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="JAXM-06" And
IDVolatilDBID_lbl.Cut_Process_Release=Yes,1,0)) AS [CUT-JAXM-06],
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="BAM-06" And
IDVolatilDBID_lbl.Sub_Assy_Release=Yes,1,0)) AS [SUB-BAM-06],
Sum(IDVolatilDBID_lbl.Qty*IIf(IDVolatilDBID_lbl.Planner="JAXM-06" And
IDVolatilDBID_lbl.Sub_Assy_Release=Yes,1,0)) AS [SUB-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;
==========================================