Comparing scores?

[Stranger]

Hi,

I have a database with two tables. One contains resident information and the other contains assessments. One resident can have many assessments. Each assessment has a score. What I need to do is subtract the last assessment score from the current assessment score and if there is a change of 2 or greater, show who the resident is with the change.

How do I do this? I have a query that will show me all resident assessments with the totals. That is as far as I have got.

Thanks,

jack

 

[Ken Snell [MVP]]

How do you know what was the last assessment score? Do you use date/time
stamp for each record? or some other means?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have a database with two tables. One contains resident information and
the other contains assessments. One resident can have many assessments.
Each assessment has a score. What I need to do is subtract the last
assessment score from the current assessment score and if there is a change
of 2 or greater, show who the resident is with the change.

How do I do this? I have a query that will show me all resident assessments
with the totals. That is as far as I have got.

Thanks,

jack

 

[Stranger]

well, there is a date stamp for when the each assessment was taken. I
figure I would have to use that as part of it. I guess I would need to
somehow be able to pick the last assessment given compared to the current.

 

[Ken Snell [MVP]]

Create a query that returns the maximum date for all residents (name this
query qryMaxDates):

SELECT ResidentID, Max(AssessmentDate) AS MaxAssmDate,
First(Assessment) AS Assmt
FROM TableName
GROUP BY ResidentID;

Then create a second query that uses qryMaxDates as one of the tables:

SELECT TableName.ResidentID, qryMaxDates.MaxAssmDate,
qryMaxDates.Assmt, Max(TableName.AssessmentDate) AS
PrevAssmDate, First(TableName.Assessment) AS PrevAssmt
FROM TableName INNER JOIN qryMaxDates
ON TableName.ResidentID = qryMaxDates.ResidentID
WHERE TableName.AssessmentDate<qryMaxDates.MaxAssmDate
GROUP BY TableName.ttt, qryMaxDates.MaxAssmDate,
qryMaxDates.Assmt;

You can use this second query to do the subtractions or to be the base table
for a third query in which you do the calcuations.
--

Ken Snell
<MS ACCESS MVP>



wrote in message news:[email protected]...