Conditional Calculations in Query.

[WhizzkidWallace]

Hi,

Here is the problem. I have a table with Rows of data in fields:

EmployeeNumber (integer)
Work Type (either a 0,1, or 2
Value (Currency)

Like this...

Employee No.........Work Type......Value
999.........................0............100.00
999.........................1..............20.00
999.........................2...............5.00

I want to turn this data into Columns like:

Employee No..........Work Type 0.....Work Type 1....Work Type 2...Total
999.........................100.00..............20.00...............5.00......125.00

etc. so that I can use an APPEND query to create a new table using SQL.

I have been trying to create conditional dummy fields to get each record into the right column like this

IIf([WorkType]=0,[Value],0) .... etc

These show up as #Err when I run the query!! Any ideas.

Getting Frustrated.....

 

[KenHigg]

IIf([WorkType]="0",[Value],0)

???
kh

 

[john471]

Crosstab Query ?

SQL:-

TRANSFORM Sum(tblYourTableName.Value) AS [The Value]
SELECT tblYourTableName.EmployeeNumber, Sum(tblYourTableName.Value) AS [Total Of Value]
FROM tblYourTableName
GROUP BY tblYourTableName.EmployeeNumber
PIVOT tblYourTableName.[Work Type];