By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

Need help to get a consecutive total from date field.

P: 3
I am trying to be able to get report of all "employees" that have had 5 consecutive days off from a "date" field in a current table.

Normally I would do this by adding the sum of two dates, however in this case I only have one date for each entry. Below is an example of the data table I am using to get this type of information:

John Doe 1/2/06
John Doe 1/3/06
Guy Day 5/4/06
Guy Day 6/22/06

The report would show John Doe as having 2 consecutive days off and Guy Day only having one day. Any assistance would be helpful.
Oct 17 '06 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,221
Troy,
Maybe you could try doing that with a crosstab query. Use the name for the row and the date for the column, and the value could be the julian day of the date. Then treat the returned dataset like an array, if 5 elements in a row have a value that one gets included.

You don't say anything about weekends ... what if a guy has off Friday and Monday, is that 2 days in a row? The Format function can return the week number of the month for a given date... if there are 5 occurrences of the same week number then you know that guy had all 5 days of the same week off.

It's an interesting puzzle to work with. Let us know what you came up with.

Jim


I am trying to be able to get report of all "employees" that have had 5 consecutive days off from a "date" field in a current table.

Normally I would do this by adding the sum of two dates, however in this case I only have one date for each entry. Below is an example of the data table I am using to get this type of information:

John Doe 1/2/06
John Doe 1/3/06
Guy Day 5/4/06
Guy Day 6/22/06

The report would show John Doe as having 2 consecutive days off and Guy Day only having one day. Any assistance would be helpful.
Oct 18 '06 #2

Expert 5K+
P: 8,434
I am trying to be able to get report of all "employees" that have had 5 consecutive days off from a "date" field in a current table.
...
The report would show John Doe as having 2 consecutive days off and Guy Day only having one day. Any assistance would be helpful.
Perhaps you coulld take a different approach and store a flag on each record to indicate whether it represent a "sequential day"? A fairly simple piece of VB code could be executed at the time the record is created, to set the flag if a record exists with the same user ID and "previous" date (taking account of weekends etc.).

If they are not necessarily created in sequence, then your routine would also need to set the flag on any existing record for the "next" day.

Does this sound like it might be any help? It should make the records much easier to count.
Oct 18 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.