comparing a record with previous record in the same table

[sametch]

I have a requirement to report improvement scores for each person for each month.

Typical data is

1 may 2003 person 1 score 35
5 may 2003 person 1 score 38
8 may 2003 person 1 score 42
1 june 2003 person 1 score 49
5 june 2003 person 1 score 60
4 may 2003 person 2 score 24
6 may 2003 person 2 score 45
2 june 2003 person 3 score 48
4 june 2003 person 3 score 65

The result I am looking for is:

MAY Person 1 improvement 7
MAY Person 2 improvement 21
JUNE Person 1 improvement 11
JUNE Person 3 improvement 17

Does anyone know a way of acheiving this with a query?

Sametch

 

[Brianwarnock]

you would need to write a series of queries.

qryfirst would select the first record for a person

groupby person groupby month min day run thsi against the table as qryfirstrecord to select the first record for each person in each month the join properties are all from the query and only matching from the table, join on all three query fields.

ditto for last records except day max

run qryimprovement which subtracts the result of qetfirstrecord from qrylastrecord join on month and person.
Note if a person only has 1 record the improvement will be zero, I guess this is correct

This is untested but it should work, use your own naming convention of course.

Brian

 

[AncientOne]

This can be done with one totals query. Group by datepart month and by person. Have min and max scores in the grid and give them aliases of MaxScore and MinScore. Then have an expression MaxScore-Minscore. This will return the overall improvement per month.

 

[Brianwarnock]

Guess I've a lot to learn yet

Brian

 

[Brianwarnock]

Err actually I don't understand the idea of using aliases, won't the aggregate function create names maxofscore and minofscore and thus the field Improvement uses the expression maxofscore-minofscore ?

Still knocks spots off my heavy approach

Brian

 

[sametch]

Brianwarnock

I implemented your method and it works fine!

Having looked at Ancientones method, i can see how that would work also, so will probably do that next!

Thanks for all your help.

Sametch

 

[AncientOne]

Brian

You don't have to use the aliases, but you can give the fields "friendlier" names if you do.

Here's a sample of my query

 

[Brianwarnock]

Thanks

That's neat I wish I had known that trick before, it certainly aids clarity.

Brian