Can this be done in a single query?

[SimonPGreen]

Hi,

The forum recently gave me a solution to a problem regarding the use of a self-join to allow calculation of the movement between two fields. Is it possible to write a single query that does the following:

Packages Table with pkPackageRef,strPackageName
Items table with pkItemRef,curValue,fkPackageRef

1:Many relationship

I wish to return the movement on all rows in the items table for a specific or all packages.

The items table movement is returned by a select and LEFT JOIN to itself on items.pkItemRef-1 however I can't seem to work how to nest that in a select strPackageName from Packages statement.

I can do it by creating a query that calls the movement query with the relationship defined however that strikes me as cheating!!!

Appreciate any help.

Simon