469,334 Members | 4,230 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,334 developers. It's quick & easy.

Need help to get a consecutive total from date field.

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
2 1652
jimatqsi
1,260 Expert 1GB
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
Killer42
8,435 Expert 8TB
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.

Similar topics

5 posts views Thread by fwells11 | last post: by
4 posts views Thread by Yonih | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.