Query between 1st and 15th of month

[astrom33]

Hi everyone. I have a table hundreds of thousands of entries showing employees' name, store number worked at, date worked, timein, and timeout. I made a query which gives me the name, store number, date, timein, timeout, and hours by employee as follows:

ID Name StoreNumber Date TimeIn TimeOut Hours
55042 Joseph 14 2/2/2008 9:00:00 AM 7:00:00 PM 9.5
55043 Joseph 14 2/7/2008 9:30:00 AM 8:00:00 PM 10
55044 Joseph 14 2/8/2008 9:30:00 AM 8:00:00 PM 10
55066 Joseph 14 2/9/2008 9:30:00 AM 7:00:00 PM 9
55067 Joseph 14 2/10/2008 10:30:00 AM 6:00:00 PM 7
55068 Joseph 14 2/14/2008 9:30:00 AM 8:00:00 PM 10
55069 Joseph 14 2/25/2008 9:30:00 AM 8:00:00 PM 10
55083 Joseph 14 2/23/2008 9:30:00 AM 7:15:00 PM 9.25
55084 Joseph 14 2/24/2008 10:30:00 AM 6:00:00 PM 7
55085 Joseph 14 2/28/2008 9:30:00 AM 8:00:00 PM 10
55086 Joseph 14 2/29/2008 9:30:00 AM 8:00:00 PM 10
55101 Joseph 14 2/16/2008 9:30:00 AM 7:15:00 PM 9.25
55102 Joseph 14 2/17/2008 10:30:00 AM 6:00:00 PM 7
55103 Joseph 14 2/18/2008 9:30:00 AM 7:30:00 PM 9.5
55104 Joseph 14 2/21/2008 9:30:00 AM 7:30:00 PM 9.5
55105 Joseph 14 2/22/2008 9:30:00 AM 7:30:00 PM 9.5


I would like to take my query further and be able to show the total hours worked by a certain employee between the 1st and 15th of the month and the 16th and last day of the month.

Can someone please help?

Thank you in advance.

 

[MarkK]

Would something like this work in an SQL select clause?

SELECT 
  Sum(IIf(Day(Date) < 16, Hours, 0)) As TopHalfOfMonth, 
  Sum(IIf(Day(Date) > 15, Hours, 0)) As BottomHalfOfMonth
FROM
  table
WHERE
  ...

 

[vbaInet]

It would lagB, but I would change the 0 to Null so it ignores it rather than include it in the sum.

 

[astrom33]

I am sure I am doing something wrong, but I get the following error message:
"Compile error. in query expression 'Sum(IIf(Day(Date) < 16, Hours, Null))' "

Any ideas what I could be doing wrong.

 

[boblarson]

It would lagB, but I would change the 0 to Null so it ignores it rather than include it in the sum.

0 included in the sum is inconsequential because it is 0 - it adds nothing to it. The only thing it could affect is AVERAGES, or some other function like the Standard Deviation, etc.

I think you can't use NULL here or else the Sum is going to choke.

 

[boblarson]

I am sure I am doing something wrong, but I get the following error message:
"Compile error. in query expression 'Sum(IIf(Day(Date) < 16, Hours, Null))' "

Any ideas what I could be doing wrong.

Get rid of the NULL and put it back to 0 as lagbolt had it. You can't sum nulls and it gives an error if you have it like that.

 

[astrom33]

I tried with the 0 instead of Null and got the same message.

 

[MarkK]

Can you post the entire query text and any code you run that executes the query.

 

[boblarson]

Looks to me, under further examination, that you are missing something:

Sum(IIf(Day(Date)) < 16, [Hours], 0))

I put the square brackets in just to aid it if you use it in the QBE grid it likes to see those in expressions. And the paren was missing from the Day function.

 

[vbaInet]

0 included in the sum is inconsequential because it is 0 - it adds nothing to it. The only thing it could affect is AVERAGES, or some other function like the Standard Deviation, etc.

I think you can't use NULL here or else the Sum is going to choke.

Not quite the case. The Null is not the cause of the error and it won't choke. It will simply ignore the Null so that is 1 less step for the Sum, but if you have a 0 it will include it in the calculation. This is the case for most aggregate functions like Avg, Count etc. Remember, if we were to Sum on a field that contains some Nulls the function happily ignores them.

I would go back to using Null.

 

[boblarson]

, but if you have a 0 it will include it in the calculation.

Not a problem in this case as we are getting a sum and not using the count for aggregate functions. And it really doesn't take a step away anyway. It still goes through all records whether it is null or 0.

 

[vbaInet]

Not a problem in this case as we are getting a sum and not using the count for aggregate functions. And it really doesn't take a step away anyway. It still goes through all records whether it is null or 0.

The engine does something like this when summing:

SummedValues = SummedValues + NextValueToSum

As we know the Sum() function ignores Nulls. If we had this, Sum([SomeField]), I wonder if the engine does this:

SummedValues = SummedValues + Nz(NextValueToSum, 0)

Or this:

If Not IsNull(NextValueToSum) Then
      SummedValues = SummedValues + NextValueToSum
End If

I would imagine it does the latter because there's no reassignment for Nulls, hence, a saving.

 

[boblarson]

The engine does something like this when summing:

SummedValues = SummedValues + NextValueToSum

I do not believe that is the way the Jet Engine works. But even if it does, adding a zero or seeing that it is null, it still has to perform a check of the field, so it does NOT save processing steps.

 

[vbaInet]

Non re-assignement is always a saving, although not physically significant, but in the eyes of the engine it is a saving.

 

[datAdrenaline]

I am unsure of the specific code contained in the Sum() function that the Jet/ACE database engine calls upon. It is likely that no one really does, outside of MS. So, to help us assume the process that Sum() uses, I derived a test using a local table with 668391 records in it, and a small code block that looks like this:

Public Function CompareSums()
 
    Dim lngTicks As Long
    Dim strSQL As String
 
    'strSQL = "SELECT Sum(0) AS MySum FROM tblMyTbl"
    strSQL = "SELECT Sum(Null) AS MySum FROM tblMyTbl"
 
    With CurrentDb
        lngTicks = GetTickCount 'API call to OS tick count
        With .OpenRecordset(strSQL)
            Debug.Print GetTickCount - lngTicks
        End With
    End With
 
End Function

I uncommented and commented appropriately. By Passing a Null, the test yielded an average time of 0.375 seconds, while passing a 0 yeilded an average of 0.750 seconds.

From my testing, passing a 0 does take just a bit longer than passing a Null. With this result I can only assume the logic in Sum() is testing for a Null and exiting the function prior to adding the value.

But ... despite the difference in processing time (which is pretty much negligible), there are some who like the readability of using the 0, but for some the use of Null is just as readable because they know how Sum() works and the use of does not throw them off. I completely understand Sum() and what it does and how it works -- I still tend to use 0 ... but Null is definately faster!

---

Now ... the SQL statement for use in a Query Object to do as requested, the following should do the trick if the field names match what has been posted. By the way, if those are the field names, I would highly encourage the changing of "Name" and "Date" to something else because both identifiers are reserved words and can create confusion --- in both the Access application and the reader of the SQL!

[FONT=Verdana]SELECT [Name][/FONT]
[FONT=Verdana]    , StoreNumber[/FONT]
[FONT=Verdana]    , Format([Date],"yyyy-mm") As MonthOf[/FONT]
[FONT=Verdana]    , Sum(IIf(Day([Date]) < 16, Hours, 0)) As MidMonthTotal[/FONT]
[FONT=Verdana]    , Sum(IIf(Day([Date]) >= 16, Hours, 0)) As EndOfMonthTotal[/FONT]
[FONT=Verdana]FROM yourTablename[/FONT]
[FONT=Verdana]GROUP BY [Name][/FONT]
[FONT=Verdana]       , StoreNumber[/FONT]
[FONT=Verdana]       , Format([Date],"yyyy-mm")[/FONT]
[FONT=Verdana]ORDER BY [Name], StoreNumber, Format([Date],"yyyy-mm")[/FONT]

----

Hope this helps!

 

[boblarson]

Thanks for posting Brent and I'll admit I was incorrect about the null vs zero as far as speed goes (but as you say it would take a heck of a lot of records to really make a difference).

 

[vbaInet]

That was an interesting analysis Brent.