Is there a function similar to Dlookup in CR? I have a table similar to this:
ID STATUS TIME
2 IN 1/1/2009 8:00
2 OUT 1/1/2009 12:00
3 IN 1/1/2009 7:30
3 OUT 1/1/2009 11:45
4 IN 1/2/2009 9:15
4 OUT 1/2/2009 13:00
I have made a pivot that generates something like this:
ID IN OUT
2 1/1/2009 8:00 1/1/2009 12:00
3 1/1/2009 7:30 1/1/2009 11:45
4 1/2/2009 9:15 1/2/2009 13:00
Now what I need is to calculate the time lapsed between punches and add a third column to the previous pivot titled HOURS WORKED. I have managed to get it to work using MIN and MAX functions but the moment there is a second IN or OUT entry for the same ID... it does not work anymore. Access has DLOOKUP where you can lookup the value of a field based on certain criteria. I can't use a stored procedure. Any ideas how to accomplish this? Any help would be appreciated.
ID STATUS TIME
2 IN 1/1/2009 8:00
2 OUT 1/1/2009 12:00
3 IN 1/1/2009 7:30
3 OUT 1/1/2009 11:45
4 IN 1/2/2009 9:15
4 OUT 1/2/2009 13:00
I have made a pivot that generates something like this:
ID IN OUT
2 1/1/2009 8:00 1/1/2009 12:00
3 1/1/2009 7:30 1/1/2009 11:45
4 1/2/2009 9:15 1/2/2009 13:00
Now what I need is to calculate the time lapsed between punches and add a third column to the previous pivot titled HOURS WORKED. I have managed to get it to work using MIN and MAX functions but the moment there is a second IN or OUT entry for the same ID... it does not work anymore. Access has DLOOKUP where you can lookup the value of a field based on certain criteria. I can't use a stored procedure. Any ideas how to accomplish this? Any help would be appreciated.
