conditional calculations in queries

[phillyon]

I have a query listing: Date, Session Length, StaffName1, StaffName2, StaffName3

I'd like the next column to be Total Staff Hours, being a straightforward calculation of length x the number of staff working that session.

sometimes we don't have a second and third staff member and those columns will return blank.

How can I return the calculation based on these conditions?

[Lysander]

I have a query listing: Date, Session Length, StaffName1, StaffName2, StaffName3

I'd like the next column to be Total Staff Hours, being a straightforward calculation of length x the number of staff working that session.

sometimes we don't have a second and third staff member and those columns will return blank.

How can I return the calculation based on these conditions?

I would write a global function that took the length and the 3 staff names, with the staffnames being optional, and then calculated and returned the value wanted. Then call this function in your query.

[phillyon]

please could you expand on how to go about that, I'm not very experienced on Access

[MMcCarthy]

I have a query listing: Date, Session Length, StaffName1, StaffName2, StaffName3

I'd like the next column to be Total Staff Hours, being a straightforward calculation of length x the number of staff working that session.

sometimes we don't have a second and third staff member and those columns will return blank.

How can I return the calculation based on these conditions?

This is why the StaffNames should not be listed like this. Have a look at this tutorial

Normalisation and Table structures

[Lysander]

please could you expand on how to go about that, I'm not very experienced on Access

As mmccarthy suggests, the tables should be normalised, but a function to solve your problem would go like this. Put the following function in a global module

Function intTotalHours(intLength As Integer, varName1 As Variant, varName2 As Variant, varName3 As Variant) As Integer
'you have to use variant type because you are expecting nulls
'I am assuming hours are integers
Dim intCount As Integer
intCount = 0
If Not IsNull(varName1) Then intCount = intCount + 1
If Not IsNull(varName2) Then intCount = intCount + 1
If Not IsNull(varName3) Then intCount = intCount + 1
intTotalHours = intLength * intCount
End Function
 

and then put the function into your query like this.

SELECT Date, Session Length, StaffName1, StaffName2, StaffName3,intTotalHours([Session Length],StaffName1, StaffName2, StaffName3) AS Total_Staff_Hours
FROM tablename;
 

Hope that helps

[phillyon]

thanks for all your helps, it has helped me approach it for the future but has made my life more difficult in the immediate.

I understand why normalising is good now.

I would like to do this, but how can I present the information. for instance, normalised I guess I would have another table, SESSIONSTAFF which contains

TableID, SessionID, StaffID
1 - 12 - Phil
2 - 12 - Stu
3 - 13 - Craig

This is sure useful for my initial question of how to report on how many hours have been worked and who's worked them etc.

However, I now have the problems of inputting the info? In my session form, can I open a staffing subform for that session and use tick boxes?

I also have the new problem of displaying that information on my day to day forms.

Is there a query format where I can lookup the staff say for session no. 12 and it gives me a text string of "Phil and Stu" or session 13 it would just say "Craig" etc.

Any develpoment ideas welcome. Thanks everyone.