Here’s a technique for getting “first” or “last” from a query..
Create a main query with the table of values to get the results of first or last)
Example:
To get the first or last PO number for a list of parts
in main query include Part table and filter it for the parts of interest.
Then create a sub query for the source of the first or last info
Example:
In subquery, use PODetail linked to POHeader
Include fields:
PONum
PODate
and any other information of interest (such as Vendor number, quantity, etc.)
Add a calculated field in subquery:
For earliest PO
Define a new calculated variable named (say)
Found
as nvarchar 8
And for earliest PO, enter the formula:
Case
when Row_Number() OVER (Partition By PODetail.PartNum
ORDER BY POHeader.OrderDate)=1
then 'YES'
else 'NO'
end
or
for latest PO, enter the formula:
Case
when Row_Number() OVER (Partition By PODetail.PartNum
ORDER BY POHeader.OrderDate DESC)=1
then 'YES'
else 'NO'
end
Add Subquery to main query and link the Subquery to Part by PartNum
Filter on SubQuery for Found=YES
Include fields from subquery (field names defined in subquery will pull through to main query).