counting contiguous series of similar field contents

[Howard]

I have a table containing (amongst other things) a person id, a date and
a score (an integer value from -4 to +4).

First I want to total the score for each person for each week. Maybe
some sort of group by?

Then the hard part. I want to count for how many consecutive weeks -
from today backwards - their weekly total has remained either negative
or positive and which it was. I want a report at the end so don't mind
if its done in a report rather than a query.

ie if sombody had these weekly totals

last week -2
week before -3
before that -2
before that +2
before that 0

they would get Negative 3 as they have had three weeks in a row (up to
today) with negative scores

sombody with
last week +2
week before -3
before that -2
before that +2
before that 0

would get Positive 1 as from today they have only had one week on the
run that is positive

There is no limit to the number of consecutive weeks that might all have
positive or negative totals. The counting of consective weeks stops
when, from today backwards, they change from a positive to negative
total or vice versa or have a total of zero at some point.

This has got me stumped!
Howard

 

[KARL DEWEY]

This seems to work using these three queries --
Howard_1 --
SELECT YourTable.PersonID, Max(YourTable.REG_AUDIT_DATE) AS
MaxOfREG_AUDIT_DATE, First(YourTable.Score) AS FirstOfScore
FROM YourTable
GROUP BY YourTable.PersonID
ORDER BY YourTable.PersonID, Max(YourTable.REG_AUDIT_DATE) DESC;

Howard_2 --
SELECT YourTable.PersonID, YourTable.REG_AUDIT_DATE,
Howard_1.MaxOfREG_AUDIT_DATE, IIf([Score]>0,"Pos","Neg") AS Score_Direction,
IIf([FirstOfScore]>0,"Pos","Neg") AS End_Score_Direction,
DateDiff("w",[REG_AUDIT_DATE],[MaxOfREG_AUDIT_DATE]) AS Expr1
FROM YourTable INNER JOIN Howard_1 ON YourTable.PersonID = Howard_1.PersonID
WHERE (((IIf([Score]>0,"Pos","Neg"))=IIf([FirstOfScore]>0,"Pos","Neg")))
ORDER BY YourTable.PersonID, YourTable.REG_AUDIT_DATE DESC;

SELECT Howard_2.PersonID, Howard_2.MaxOfREG_AUDIT_DATE AS Report_Week,
Howard_2.Score_Direction, Count([Howard_2].[REG_AUDIT_DATE])+1 AS
Consecutive_Weeks
FROM Howard_2 INNER JOIN Howard_2 AS Howard_2_1 ON Howard_2.PersonID =
Howard_2_1.PersonID
WHERE (((Howard_2_1.Expr1)=[Howard_2].[Expr1]+1))
GROUP BY Howard_2.PersonID, Howard_2.MaxOfREG_AUDIT_DATE,
Howard_2.Score_Direction;

 

[Howard]

I tried this and something seemed to happen but I was getting continuous
runs of 534 weeks which is not possible (I don't have 10 yrs of data!)
Was I supposed to run this on the raw data or on the results of a query
that had already grouped the data into weeks?
Howard
(I do understand sql as I teach it but I did find it a little tricky to
follow this one so don't really know if I need to tweak it)

 

[KenSheridan via AccessMonster.com]

Try this to sum the scores per person per week:

SELECT [Person ID],
[YourDate] - Weekday([YourDate],1)+1 AS WeekBeginning,
SUM([Score]) As TotalScore
FROM [YourTable]
GROUP BY [Person ID],
[YourDate] - Weekday([YourDate],1)+1;

To count the consecutive sequence of negative/positive scores up to the
latest date per person try the following query based on the above query
(named YourQuery in the example below)

SELECT [Person ID], COUNT(*) AS ConsecutiveWeeks,
"Negative" AS [Positive/Negative]
FROM [YourQuery] As Q1
WHERE TotalScore < 0
AND WeekBeginning] >
(SELECT MAX([WeekBeginning])
FROM [YourQuery] As Q2
WHERE Q2.[Person ID] = Q1.[Person ID]
AND TotalScore > 0)
GROUP BY [Person ID]
UNION ALL
SELECT [Person ID], COUNT(*), "Positive"
FROM [YourQuery] As Q1
WHERE TotalScore > 0
AND WeekBeginning] >
(SELECT MAX([WeekBeginning])
FROM [YourQuery] As Q2
WHERE Q2.[Person ID] = Q1.[Person ID]
AND TotalScore < 0)
GROUP BY [Person ID];

Ken Sheridan
Stafford, England