Add additional Column based on a column value

[deepanadhi]

Hi,

Below is my sample table structure.

-------------------------------------------------------
|Name | Type | week1effort | week2effort | week3effort|
--------------------------------------------------------
Mr. A | X | 5 | 6 | 2 |
Mr. A | Y | 7 | 1 | 3 |
Mr. A | Z | 5 | 6 | 3 |
Mr. B | X | 4 | 4 | 1 |
Mr. B | Y | 6 | 2 | 3 |
Mr. B | Z | 5 | 6 | 6 |

After uploading this table from excel,


i should write a query which should give result as follows

-------------------
|Name | X | Y | Z |
-------------------
Mr.A | 13 |11 |14|
Mr.B | 9 |11 |17|

How to achieve this ?
Thanks in advance.

[recyan]

Just check if below gives some guidelines :

TRANSFORM Max(qryTotalEffortA.TotalWeekEffort) AS MaxOfTotalWeekEffort
SELECT 
	qryTotalEffortA.testName
FROM 
	(
		SELECT 
			Table1.testName, 
			Table1.Type, 
			Table1.week1effort, 
			Table1.week2effort, 
			Table1.week3effort, 
			[week1effort]+[week2effort]+[week3effort] AS TotalWeekEffort
		FROM 
			Table1
	)
	AS qryTotalEffortA
GROUP BY 
	qryTotalEffortA.testName
PIVOT 
	qryTotalEffortA.Type;

Thanks

[deepanadhi]

Hi recyan,

Thanks for the reply
and sorry for the late response got stuck up with another work..

This is my query & it throws syntax error..

transform Max([TABLE1].totalvalue as Max(totalvalue)
select [TABLE1].[Itemname]
from (
select
[TABLE1].[Itemname],
[TABLE1].[type],
[TABLE1].[Effort1],
[TABLE1].[Effort2],
[TABLE1].[Effort3],
[TABLE1].[Effort4],
[TABLE1].[Effort1]+[TABLE1].[Effort2]+[TABLE1].[Effort3]+[TABLE1].[Effort4] as totalvalue
from
[TABLE1])
as [TABLE1].totalvalue
group by [TABLE1].[Itemname]
pivot [TABLE1].[type];

please revert whare have i made the misatake...thanks in advance..

Deepan

[recyan]

Check out below

	
Transform Max(qryTABLE1.totalvalue as Max(totalvalue)
select 
	[TABLE1].[Itemname]
from 
	(
		select
			[TABLE1].[Itemname],
			[TABLE1].[type],
			[TABLE1].[Effort1],
			[TABLE1].[Effort2],
			[TABLE1].[Effort3],
			[TABLE1].[Effort4],
			[TABLE1].[Effort1]+[TABLE1].[Effort2]+[TABLE1].[Effort3]+[TABLE1].[Effort4] as totalvalue
		from
			[TABLE1]
	)
	as qryTABLE1
group by 
	qryTABLE1.Itemname
pivot 
	qryTABLE1.type;

Thanks

[deepanadhi]

Hi Recyan,

Thanks for the quick reply.
Can you be more specific, what is that qryTable1 ?
Is it the name of the new query am creating now?

Thanks,
Deepan

[recyan]

(
		select
			[TABLE1].[Itemname],
			[TABLE1].[type],
			[TABLE1].[Effort1],
			[TABLE1].[Effort2],
			[TABLE1].[Effort3],
			[TABLE1].[Effort4],
			[TABLE1].[Effort1]+[TABLE1].[Effort2]+[TABLE1].[Effort3]+[TABLE1].[Effort4] as totalvalue
		from
			[TABLE1]
	)
	as qryTABLE1

qryTABLE1 is the name ( alias ) for the sub query ( shown above) that is there inside your main Query.
First check if the thing works. Then, if it works ???, read the query slowly. You should get it.

Thanks

[deepanadhi]

Thanksalot...
Got it...now working..

Thanks a ton for the responses.

- Deepan

[recyan]

H2H - Happy To Help

Thanks