two columns in Query (BAM-06, JAXM-06)

[ldiaz]

I have this query

==================================================
SELECT DISTINCTROW IDVolatilDBID_lbl.User6, Sum(IDVolatilDBID_lbl.Qty) AS
[Sum Of Qty]
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"))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY Sum(IDVolatilDBID_lbl.Qty) DESC;

===================================================
and this shows two colums like this:
User6 Sum Of Qty
SUB-ASSY MEX 4312

What I want, is to change this to three columns:
User6 Sum Of Qty (BAM-06) Sum Of Qty (JAXM-06)
SUB-ASSY MEX 4312 1500

IDVolatilDBID_lbl.Planner contain datas as BAM-06 and JAXM-06

please help

 

[KARL DEWEY]

Try this --
SELECT DISTINCTROW IDVolatilDBID_lbl.User6,
Sum(IIf([Planner]="BAM-06",[QTY],0)) AS [Sum Of BAM-06],
Sum(IIf([Planner]="JAXM-06",[QTY],0)) AS [Sum Of 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"))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY Sum(IIf([Planner]="BAM-06",[QTY],0)) DESC ,
Sum(IIf([Planner]="JAXM-06",[QTY],0)) DESC;

 

[Ken Sheridan]

Lorenzo:

Try this:

SELECT IDVolatilDBID_lbl.User6,
SUM(IDVolatilDBID_lbl.Qty*IIF(IDVolatilDBID_lbl.Planner = "BAM-06",1,0)) AS
[Sum Of Qty (BAM-06)],
SUM(IDVolatilDBID_lbl.Qty*IIF(IDVolatilDBID_lbl.Planner = "JAXM-06",1,0)) AS
[Sum Of Qty (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"))
GROUP BY IDVolatilDBID_lbl;

I'll leave you to decide on what order you now want the rows returned in.

Ken Sheridan
Stafford, England

 

[ldiaz]

it shows a message:
====================================================
The specified field '[Planner]' could refer to more that one table listed in
the FROM clause of your SQL statement.
====================================================

how fix this?
--
Lorenzo Díaz
Cad Technician

Try this --
SELECT DISTINCTROW IDVolatilDBID_lbl.User6,
Sum(IIf([Planner]="BAM-06",[QTY],0)) AS [Sum Of BAM-06],
Sum(IIf([Planner]="JAXM-06",[QTY],0)) AS [Sum Of 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"))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY Sum(IIf([Planner]="BAM-06",[QTY],0)) DESC ,
Sum(IIf([Planner]="JAXM-06",[QTY],0)) DESC;

--
KARL DEWEY
Build a little - Test a little

I have this query

==================================================
SELECT DISTINCTROW IDVolatilDBID_lbl.User6, Sum(IDVolatilDBID_lbl.Qty) AS
[Sum Of Qty]
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"))
GROUP BY IDVolatilDBID_lbl.User6
ORDER BY Sum(IDVolatilDBID_lbl.Qty) DESC;

===================================================
and this shows two colums like this:
User6 Sum Of Qty
SUB-ASSY MEX 4312

What I want, is to change this to three columns:
User6 Sum Of Qty (BAM-06) Sum Of Qty (JAXM-06)
SUB-ASSY MEX 4312 1500

IDVolatilDBID_lbl.Planner contain datas as BAM-06 and JAXM-06

please help