Query question

K

Kelly

My table:


Employee No Work Code No of hours Start Value
2 4.0
2 89 2
2 92 1
2 92 1
2 92 2


Here is my query and the results I am trying to get are:

Work Code 89 is Earned
Work Code 92 is Taken


The start value is 4 – so I would need to add work code 89 and then minus 92
and then the result is 2. How would I set up a query?

Thanks,
 
I

Ian B

Hi Kelly,

In your add a field with following:-

For_Total: IIf(IsNull([Work Code]),[Start Value],IIf([Work Code]=89,[No of
hours]*-1,IIf([Work Code]=92,[No of hours],0)))

This will put a figure in the For_Total Column.

Then run another query with [employee no] group by with the [For_Total] as
the sum

This will give you a total for each employee
 
Top