Crosstab Query - Dates?

Radach

New member
Local time
Today, 13:04
Joined
Oct 10, 2005
Messages
7
Hello,

I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.

Field: LogDate
Table: tblWorkLog
Total: Count
Corsstab: Value

Is there anyway to exclude weekends and holidays from that value?

I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?

***Addition***

Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.

So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
 
Last edited:
Thank you Scott. I have taken a look at that site. And that would have been great if I had 2 dates that I needed to calculate the difference of.

In this situation, I have only 1 Date Field, which is logged when the user inputs his or her work for the day.

I have realized something I should have earlier, feel stupid for not doing so. But the count feature is actually counting the # of log date entries.

If for instance Lisa logged on 10/10/05 and entered the work she did for the afternoon. Then she needed to make some corrections to her work. So she logged on again on 10/10/05 to enter the additional work she did. Using the coung feature it woud say it took her 2 days to complete her task, but in actuality it was only 1.

I searched this form and would this formula do me any good and if so how would I incorporate it in the crosstab query.

NumberofWorkDays: (Weekday([logdate])-DCount("*","[tblHolidays]"))+1
 
I think you need to rethink your methodology here. I can't see how you can get an accurate statistic using this methodology. All you seem to have is a record of when a user logged in to record work details. But that's not a valid measure. As you point out yourself, a user can log in multiple times in the same day.

The only way to accurately record the info you want is with a table like:

tblWorkTime
WorkTimeID (PK Autonumber)
EmployeeID (FK)
TaskID (FK)
TimeStamp (Date/Time)
TimeType (Start/End)
 
Thank you Scott.
I have created a table with that information in it.

And then how would I incorporate this into determining how many days it took to complete one of the tasks?
 
Well there are a couple of ways. But one way would be to create a query like this using Query Design mode. First add tblWorkTime to the query. Next add the EmployeeID and TaskID columns to the query. Then add a column with the expression:

ElapsedDays: IIF([TimeType]="End",[TimeStamp],[TimeStamp]*-1)

Now you can use this query as the source for your crosstab and it should give a total of elapsed days per employee, per task
 

Users who are viewing this thread

Back
Top Bottom