Finding predecessors within a table

[Karyn]

I have a table of contracts. These contracts are renewed every year, and therefore many contracts have a version from the previous year, but some are new and have no predecessor. I want to write a query which lists all contracts on the table, together with a reference to any preceding contract with the same customer, or a null if there is none. For example, if the table fields are contract number, customer and year, and the table looks like this:


Contract Customer Year
271 Smith 2013
816 Brown 2013
101 Jones 2013
227 Smith 2012
564 Brown 2012

I want a query which returns:

Contract Customer Year Previously
271 Smith 2013 227
816 Brown 2013 564
101 Jones 2013 null
227 Smith 2012 null


564 Brown 2012 null

[orange]

So, build the query (this should give you a starting point)

SELECT * from yourTable where Customer = "Smith"
Order By ContractYear Desc

Year is a reserved word in Access

Good luck