QUERY FOR LASTEST OPERATION

Zach:

To return both the location and the latest date per OrderNumber/LineItem
you'll need to use a subquery to find the latest date and use this date to
restrict the outer query, e.g.

SELECT *
FROM [YourTable] AS T1
WHERE [date] =
(SELECT MAX([date])
FROM [YourTable] AS T2
WHERE T2.[ordernumber] = T1.[ordernumber]
AND T2.[lineitem] = T1.[lineitem]);

The aliases T1 and T2 are used to distinguish the two instances of the
table, enabling the subquery to be correlated with the outerquery.

BTW I'd avoid using date as a column name. It’s the name of a built in
function, so its better to use something specific like transactiondate. If
you do use date then be sure to wrap it in square brackets, [date], when
referencing the column in a query or in code.

Ken Sheridan
Stafford, England

"ZigZagZak" wrote:

> Hi I have a database that tracks where parts are in our shop. I have a table
> that we enter where a part was put and it gets taged with the date. I am
> trying to make a query to find the latest [date]/[location] for a
> [OrderNumber]/[LineItem].
>
> Can anyone help me set up this query?
>
> The plan is to make a report showing department load. I already have a
> query finding the average parts through a department. Now I just need how
> many are currently in it.
>
> Thanks in advance.
>
> Zach
 >> Stay informed about: QUERY FOR LASTEST OPERATION