Summarise groups of data

I've got some data which looks a bit like this:

row_id user sick day sick period
1 A 02/01/2004 1
2 A 03/01/2004 1
3 A 30/01/2004 2
4 B 02/01/2004 1
5 B 01/02/2004 1
6 B 01/03/2004 1


I need a query, or set of queries to populate the sick
period column, which has been filled out manually here, so
I can summarise by user and number of sick periods. You
can see how the rules work; periods of sickness are
grouped together.

Anyone got any ideas ?

Thanks


Damien

Maybe that is clear to you, but I can't understand the rules that make Sick
Period 1 or 2 or any other number

I would think that you might be able to use a totals query and not need to fill
in Sick Period.

SELECT User, Count(SickDay) as DateCount
FROM YourTable
GROUP BY User

In the query grid, add in the User and Sick Day field
Select View: Total from the menu

In the Total row, change Group By to Count under the Sick Day Field.

damien wrote:

I've got some data which looks a bit like this:

row_id user sick day sick period
1 A 02/01/2004 1
2 A 03/01/2004 1
3 A 30/01/2004 2
4 B 02/01/2004 1
5 B 01/02/2004 1
6 B 01/03/2004 1

I need a query, or set of queries to populate the sick
period column, which has been filled out manually here, so
I can summarise by user and number of sick periods. You
can see how the rules work; periods of sickness are
grouped together.

Anyone got any ideas ?

Thanks

Damien

If the sick days are contigious, they are counted as one
sick period; ie if you have 2 days off sick in January,
next to each other, they are counted as one period. If
you then have two days off sick in February, but they are
not next to each other, eg 01/02/2004 and 28/02/2004, you
have had 4 days off sick, constituting 3 sick periods.

-----Original Message-----
Maybe that is clear to you, but I can't understand the
rules that make Sick
Period 1 or 2 or any other number

I would think that you might be able to use a totals
query and not need to fill
in Sick Period.

SELECT User, Count(SickDay) as DateCount
FROM YourTable
GROUP BY User

In the query grid, add in the User and Sick Day field
Select View: Total from the menu

In the Total row, change Group By to Count under the Sick
Day Field.

damien wrote:

I've got some data which looks a bit like this:

row_id user sick day sick period
1 A 02/01/2004 1
2 A 03/01/2004 1
3 A 30/01/2004 2
4 B 02/01/2004 1
5 B 01/02/2004 1
6 B 01/03/2004 1

I need a query, or set of queries to populate the sick
period column, which has been filled out manually here,
so
I can summarise by user and number of sick periods. You
can see how the rules work; periods of sickness are
grouped together.

Anyone got any ideas ?

Thanks

Damien
.

Ok, now I understand what you want to do. Too bad, I don't know how to do what
you want. Hopefully someone else will pick up the thread and have a solution.
In the meantime, I will ponder this - probably this weekend - and see if
inspiration strikes.

I can envision a way to do this using recordsets and VBA code.

Some other question(s) for you, that could change the solution.

Does the numbering of sick periods start over at the end of the year or some
other time frame? If so, how do you count Dec 31, 1999 and Jan 1, 2000? Two
different sick periods?

Does January 31 and February 1 constitute one sick period? In other words,
consecutive days not in the same month are still one sick period.

How about sick days on Friday and Monday? Assuming that someone has sickday on
Friday and has no record of sickday on Saturday or Sunday and then has a record
of sickday on Monday.

wrote:

If the sick days are contigious, they are counted as one
sick period; ie if you have 2 days off sick in January,
next to each other, they are counted as one period. If
you then have two days off sick in February, but they are
not next to each other, eg 01/02/2004 and 28/02/2004, you
have had 4 days off sick, constituting 3 sick periods.

-----Original Message-----
Maybe that is clear to you, but I can't understand the
rules that make Sick
Period 1 or 2 or any other number

I would think that you might be able to use a totals
query and not need to fill
in Sick Period.

SELECT User, Count(SickDay) as DateCount
FROM YourTable
GROUP BY User

In the query grid, add in the User and Sick Day field
Select View: Total from the menu

In the Total row, change Group By to Count under the Sick
Day Field.

damien wrote:

I've got some data which looks a bit like this:

row_id user sick day sick period
1 A 02/01/2004 1
2 A 03/01/2004 1
3 A 30/01/2004 2
4 B 02/01/2004 1
5 B 01/02/2004 1
6 B 01/03/2004 1

I need a query, or set of queries to populate the sick
period column, which has been filled out manually here,
so
I can summarise by user and number of sick periods. You
can see how the rules work; periods of sickness are
grouped together.

Anyone got any ideas ?

Thanks

Damien
.