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

Need query Formula for consecutive days.

P: 3
I need a query formula to total consecutive days for dates. Employees are listed each time they are absent with a new date. Is this type of calculation possible without having two date fields?

tble:
Employee ID Type of Absence Date
Oct 17 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Then using VBA code open a recordset of a query ordered by EmployeeID and AbsenseDate

Then run through the records consecutively to see if date2 = date1 +1 etc.

I need a query formula to total consecutive days for dates. Employees are listed each time they are absent with a new date. Is this type of calculation possible without having two date fields?

tble:
Employee ID Type of Absence Date
Oct 17 '06 #2

P: 3
mmccarthy,
Your on the right track of what I want Thank You, however we are talking about 10,000 entries. Is there a way to total the consecutive days by EmployeeID. What I need is a list of EmployeeID that has 5 or more consecutive day absence.
Oct 17 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I can't think of a way to do what you're asking offhand.

It shouldn't take a recordset long to compare the values in 10,000 records
you just need to get your logic right.

I would suggest as a start you open a second recordset of employees with a distinct list of names and run through the first recordset name by name.


mmccarthy,
Your on the right track of what I want Thank You, however we are talking about 10,000 entries. Is there a way to total the consecutive days by EmployeeID. What I need is a list of EmployeeID that has 5 or more consecutive day absence.
Oct 17 '06 #4

Post your reply

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