Dividing sum by 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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 said:
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.......
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.......
Click to expand...
Click to expand...
Click to expand...
 
Back
Top