Find the next to last record

I have a table containing staff information with stID as key.

I have another table with the following :-

ID
stID
ChangeDate
Salary
Comments

I can find the last change for each staff member but I am trying to list the change before that.

Can anyone help?

Adrian

Subselect works in this instance.
SELECT A.* from MyTable A
Where A.ChangeDate = (SELECT Max(B.ChangeDate) from MyTable B where B.changedate <> (Select max(C.ChangeDate) from MyTable C where C.stID = B.stID) AND B.stID = A.stID)

(Off the top of my head, so may need tweaking)
Now Access doesn't play that well with subselects, so if you have large tables, you may be better off trying to break it apart into multi-queries rather than doing the subselect thing, they work in access, but sometimes they work slowly. I have gained many minutes by breaking apart queries into many, but that adds complications also.

Thanks very much for your assistance.

I have managed to select the next to last record from the table but what I was trying to do was to select the next to last change for every employee.

This may not be possible.

I have tried to select the last two changes for each employee so that I can then compare this with the last change query to eliminate them but I can't seem to do that either.

Perhaps the answer is to change the table so each row contains the details of the last two changes and when you create a new record for an employee the last change is automatically copied into the previous change fields.

Any thoughts.

Thanks again

Adrian

I think a couple of queries will do the job

The first groups on stID and selects Max changedate

The next joins the output of the 1st against the table on stID, selects stID and Max of change date groupby stID where changedate<> MaxOfchangedate

Brian

Brian

Thanks

I'm sure mine is not the nicest way of doing it.

1. Find the last change for each employee.
2. List all changes apart from those in 1.
3 Find the last changes for each employee in 2.
4. Select the full details of the changes table linked to 1.
5. Select the full details of the changes table linked to 3.

Anyway, it seems to give me the information I need

Thanks

Adrian