M
Mark
Hello all,
Background:
I have a table that has a date and time stamp (one field) along with other
fields; each record is considered a task. It holds 10 days worth of data
then if it is over 10 days it gets purged. I am linking to this database, so
I am unable to change, or add to the table.
What I am trying to do:
I want to run a dynamic query/report that shows the count of the total task.
Here’s the tricky part (for me). I want to be able to view it by
shift/day/week/period. Day, week, and period are easy, but how do I separate
it per shift. And here’s the other twist our shifts are 12 hours a day with 4
shift’s a week. A shift is 5am to 5pm Mon – Wed, B shift is 5am to 5pm Thurs
– Sat, C shift is 5pm to 5am Sun – Tue, D shift is 5pm to 5am Wed – Fri.
Expected output:
I would like to have something to this effect.
Totals for:
Date Range 6/05/05 thru 6/11/05
A Shift 1563
B Shift 852
C Shift 2003
D Shift 1487
Of course if I only search for one day there would only be 2 shifts and then
I would want it to omit the others.
Date Range 6/06/05 thru 6/06/05
A Shift 428
C Shift 721
Question’s:
I guess the first question is how should I approach this?
A crosstab query would be nice, but is there a way to differentiate the date
by shifts?
Is there a way to determine which day of the week it is, and how could I do
it and the query/report know what shift it would be assigned to?
Any direction would be greatly appreciated.
Background:
I have a table that has a date and time stamp (one field) along with other
fields; each record is considered a task. It holds 10 days worth of data
then if it is over 10 days it gets purged. I am linking to this database, so
I am unable to change, or add to the table.
What I am trying to do:
I want to run a dynamic query/report that shows the count of the total task.
Here’s the tricky part (for me). I want to be able to view it by
shift/day/week/period. Day, week, and period are easy, but how do I separate
it per shift. And here’s the other twist our shifts are 12 hours a day with 4
shift’s a week. A shift is 5am to 5pm Mon – Wed, B shift is 5am to 5pm Thurs
– Sat, C shift is 5pm to 5am Sun – Tue, D shift is 5pm to 5am Wed – Fri.
Expected output:
I would like to have something to this effect.
Totals for:
Date Range 6/05/05 thru 6/11/05
A Shift 1563
B Shift 852
C Shift 2003
D Shift 1487
Of course if I only search for one day there would only be 2 shifts and then
I would want it to omit the others.
Date Range 6/06/05 thru 6/06/05
A Shift 428
C Shift 721
Question’s:
I guess the first question is how should I approach this?
A crosstab query would be nice, but is there a way to differentiate the date
by shifts?
Is there a way to determine which day of the week it is, and how could I do
it and the query/report know what shift it would be assigned to?
Any direction would be greatly appreciated.