Dividing sum by 2

I have a query that accumulate the sum of material. The query has Material,
Qty(sum), Assemble (Check Box) which is set to 'NO' and Material Type.

If the Material Type is '5' then the Qty(sum) needs to be divided by 2. If
the Material Type is any other value it would be Qty(sum).

Any Idea's.......

hi,

Try this...

SELECT tbl_name.material, IIf([material_type]='2',Qty(Sum), Qty(Sum)/2) AS
new_column_name, tbl_name.assemble
FROM tbl_name
GROUP BY tbl_name.material, tbl_name.assemble;

Hope this helps,
geebee


"JeffH13" wrote:

I have a query that accumulate the sum of material. The query has Material,
Qty(sum), Assemble (Check Box) which is set to 'NO' and Material Type.

If the Material Type is '5' then the Qty(sum) needs to be divided by 2. If
the Material Type is any other value it would be Qty(sum).

Any Idea's.......

This is what I'm starting with....
-----
SELECT DISTINCTROW [tbl Material].Type, [Material Estimate CV].Material,
Sum([Material Estimate CV].Qty) AS SumOfQty, Production.[Ass Date]

FROM [tbl Material] INNER JOIN (Production INNER JOIN [Material Estimate CV]
ON Production.[Job #] = [Material Estimate CV].[Job Number]) ON [tbl
Material].Name = [Material Estimate CV].Material

GROUP BY [tbl Material].Type, [Material Estimate CV].Material,
Production.[Ass Date], Production.Ass

HAVING (((Production.[Ass Date]) Between CDate(Date()-Day(Date())+1) And
DateSerial(Year(Date()),Month(Date())+1,0)) AND ((Production.Ass)=Yes));
-----

I having problems understand how the IIF statement should be written...
........
SELECT DISTINCTROW [tbl Material].Type, IIf([tbl
Material].Type='5',([Material Estimate CV].Material, Sum([Material Estimate
CV].Qty) AS SumOfQty, Production.[Ass Date])/2,([Material Estimate
CV].Material, Sum([Material Estimate CV].Qty) AS SumOfQty, Production.[Ass
Date])
.........
This doesn't work....
I'm I totally off base.....

Thanks for you help
"geebee" wrote:

hi,

Try this...

SELECT tbl_name.material, IIf([material_type]='2',Qty(Sum), Qty(Sum)/2) AS
new_column_name, tbl_name.assemble
FROM tbl_name
GROUP BY tbl_name.material, tbl_name.assemble;

Hope this helps,
geebee


"JeffH13" wrote:

I have a query that accumulate the sum of material. The query has Material,
Qty(sum), Assemble (Check Box) which is set to 'NO' and Material Type.

If the Material Type is '5' then the Qty(sum) needs to be divided by 2. If
the Material Type is any other value it would be Qty(sum).

Any Idea's.......

hi,

The syntax for your IIf column in the query would be...

IIf([tbl
Material].Type='5',(Sum([Material Estimate CV].Qty)/2, Sum([Material
Estimate CV]))
AS SumOfQty

I noticed you are taking from 2 tables? If so, I see no join clause in
there joining the 2 tables. If you are using a nested query, then this
should work.

Hope this helps,
geebee


"JeffH13" wrote:

This is what I'm starting with....
-----
SELECT DISTINCTROW [tbl Material].Type, [Material Estimate CV].Material,
Sum([Material Estimate CV].Qty) AS SumOfQty, Production.[Ass Date]

FROM [tbl Material] INNER JOIN (Production INNER JOIN [Material Estimate CV]
ON Production.[Job #] = [Material Estimate CV].[Job Number]) ON [tbl
Material].Name = [Material Estimate CV].Material

GROUP BY [tbl Material].Type, [Material Estimate CV].Material,
Production.[Ass Date], Production.Ass

HAVING (((Production.[Ass Date]) Between CDate(Date()-Day(Date())+1) And
DateSerial(Year(Date()),Month(Date())+1,0)) AND ((Production.Ass)=Yes));
-----

I having problems understand how the IIF statement should be written...
.......
SELECT DISTINCTROW [tbl Material].Type, IIf([tbl
Material].Type='5',([Material Estimate CV].Material, Sum([Material Estimate
CV].Qty) AS SumOfQty, Production.[Ass Date])/2,([Material Estimate
CV].Material, Sum([Material Estimate CV].Qty) AS SumOfQty, Production.[Ass
Date])
........
This doesn't work....
I'm I totally off base.....

Thanks for you help
"geebee" wrote:

hi,

Try this...

SELECT tbl_name.material, IIf([material_type]='2',Qty(Sum), Qty(Sum)/2) AS
new_column_name, tbl_name.assemble
FROM tbl_name
GROUP BY tbl_name.material, tbl_name.assemble;

Hope this helps,
geebee


"JeffH13" wrote:

I have a query that accumulate the sum of material. The query has Material,
Qty(sum), Assemble (Check Box) which is set to 'NO' and Material Type.

If the Material Type is '5' then the Qty(sum) needs to be divided by 2. If
the Material Type is any other value it would be Qty(sum).

Any Idea's.......