Form Sql Statement

[melodyF]

I hopeful there is an easy way to do the below. I'm tying to add up my employees 1/2 day Leave and full day leave to get total leave. The only I can get this to work is to write two queries then put them together. Is there any way I can get this answer in one query. I have sickdays, FMLA, ETC.. to do and it could get very messy.. Below is what I'm know doing..


Private Sub Form_Open(Cancel As Integer)
Dim strSQL
Dim frm As Form
Dim Db As Database
Dim rs As Recordset

Set frm = Forms!frmTotals
Set Db = CurrentDb



' Half Day Leave

strSQL = "SELECT Count(InputID) AS TotDays FROM
tblInput "
strSQL = strSQL & " WHERE ((tblInput.UserID)= " & EmployeeID & ") AND ((tblInput.InputDate) Between #6/1/02# And #6/30/02#)AND ((tblInput.InputText)= '1/2 LeaveDay'); "
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)

frm!txtHalfVacJune = rs!TotDays * 0.5
rs.Close
strSQL = "'"

'FullDayLeave

strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & " WHERE ((tblInput.UserID)= " & EmployeeID & ") AND ((tblInput.InputDate) Between #6/1/02# And #6/30/02#)AND ((tblInput.InputText)= 'Leave Day'); "
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)

frm!txtLeaveJune = rs!TotDays + frm!txtHalfLeaveJune
rs.Close
strSQL = "'"



Set rs = Nothing
Db.Close
Set Db = Nothing

 

[Cosmos75]

Couldn't you just have a query (using the graphical interface) that pulls all employees, and calculates in one field via an expression, 1/2 day sick leave, in another all full day leave (an so on for others like FMLA - whatever that is) that are >0 between two dates.

And then a second query to add all the calculated expressions together for each employee.

I am useless with SLQ so I only know how to use the graphical interface! Sorry.