topvalue in a query

[mincher]

I have two tables:

o Staff table with list of all staff
o Job Position table with all positions held by staff and linked to former by unique staffID (this also has date of appointment for each position)

How do i design a query to return a list of all staff with all their most current position?

 

[ColinEssex]

If you have an appointment End Date then you can select the appointment where the end date is Null, which will give you the current positions.

Col

 

[mincher]

Brilliant - solved in 60 seconds

Many thanks Colin of Essex!