Counting Incidences and Total Days

[NOPOPNOSTYLE]

I've really been Scratching my head with this one. I've got a table structured thus.. Field 1; Attending (Text) with either a "Yes" or "No", Field 2; Date (Short Date)in accending order; Field 3 Exception (Text)only entry would be "Sick" otherwise field will be Null. The Dates are the days that people attend, therefore the next record down is not always 1 day later than the previous record. I would like to create a query that would count how many consecutive working days were taken off sick, count that as 1 incidence and return the total number of days taken for that incidence. However there will be multiple incidences and and I Would like one result for the all data looked at.

Many Thanks

Lord Exell

 

[paron]

From the number of people who have marked this without replying, this must be a real poser. Just to keep the thread alive, let me offer the following:

You want (I think) the output to look something like:

IncidentNumber DurationDays
1 3
2 1
3 6
. .
. .

Right?


And the days that people aren't supposed to attend aren't in the record? That's probably a good thing; that way we don't have to subtract weekends or holidays or "by"s or whatever.

It wouldn't be terribly difficult in VB, but I sure don't have enough SQL to make it work. Anyone else out there up for it?

Ron

 

[Taff07]

I don`t think SQL is an option with this on is it? VB should be easy enough to do. Does this table link into another with individual names details etc or is a single entity?

Ian

 

[JoanneM]

Ok I've got some time to kill I'll have a play around and see what emerges.

regards

jo

 

[NOPOPNOSTYLE]

Paron

You have hit the nail on the head, this is exactly what I need result wise.

Taff07, I do have a Names table which could easily relate to this query.

Many thanks for your time on this, I hope you can come up with the solution.

Lord Exell

 

[paron]

Well, it's been a while; I am inclined to think you're stuck with using VB. If Jo couldn't do it in this amount of time, it's a really tricky one for SQL -- probably impossible.

Ron

 

[WingandaPrayer]

If I'm not too late, try this link regarding sequences.

http://www.mvps.org/access/_vti_bin/shtml.exe/search.htm

Hope i'm on the right track???????

 

[paron]

Thanks, WingAndAPrayer, I think you found it. When I searched, the most relevant seemed to be

http://www.mvps.org/access/queries/qry0021.htm

, which looks like exactly what's needed. I'm still sold on VBA, but it is possible with SQL. How about that?

Ron