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.

 

[plog]

First, you're table isn't properly structured. Whenever you have numerated field names (i.e. week1effort, week2effort...etc) you should have an additional table which holds this data. Second, 'Name' is a reserved word in Access and makes a bad field name.

With that said, to do what you want with the data you have you need a Cross-tab query. This is the SQL for what you want with the structure you have:

TRANSFORM Sum([week1effort]+[week2effort]+[week3effort]) AS Val
SELECT YourTableNameHere.Name
FROM YourTableNameHere
GROUP BY YourTableNameHere.Name
PIVOT YourTableNameHere.Type;

Replace all instances of 'YourTableNameHere' with the name of your actual table. If you've left anything out of your question, or your structure isn't exactly as you described it, you will need to properly structure your database.