Compare records in a query

[Martin Watts]

Hi FOAK,

A database recording training course details. I have set up a query which
returns the learners ID and the level of each course they have completed as
represented below.

LEARNER ID LEVEL
6 1
6 1
8 1
8 2

I need to be able to 'compare' the records so that I can determine if the
learner has progressed. In the above example, learner 6 will not have
progressed as both courses completed were level 1, whereas learner 8 has
progressed has they have completed a level 1 course and them completed a
level 2 course.

How would you do it?

TIA

Martin Watts

 

[John Spencer (MVP)]

On the limited set of data you gave you could use min and max and group by.

SELECT [Learner ID],
IIF(Min(Level)=Max(Level),"No Advance","Advance") as Status
FROM YourTable
GROUP BY [Learner Id]

However, I assume the problem and the data is more complex than what you gave as
an example.