I am working on a group by query:
Original Table:
-pk- -pk-
AssetID TsfrDate TsfrFrom TsfrTo
1522 09/01/2002 <null> TX
1522 10/01/2002 TX NV
1523 05/01/2002 MN IL
1523 06/01/2002 IL MI
1523 04/01/2002 <null> MN
Original table records the transfer activity of assets.
The current location of asset 1522 is NV and 1523 is MI.
The qry result I need is:
AssetID Current Loc
1522 10/01/2002 TX NV
1523 06/01/2002 IL MI
Just the current location of each asset based on the
latest transfer date.
Here's what I attempted:
A sorting select query:
QrySortAssets
AssetID TsfrDate From To
ASC DESC
Then a "Group By" qry based on QrySortAssets using the
function "Last of" or "First of" to grab the first row
of each assetid grouped on from the sorted query.
However, it seems to pay no attention to the sorting of the second query, and is still looking at the original tables order....
Any ideas? thanks!
nstouffer
Original Table:
-pk- -pk-
AssetID TsfrDate TsfrFrom TsfrTo
1522 09/01/2002 <null> TX
1522 10/01/2002 TX NV
1523 05/01/2002 MN IL
1523 06/01/2002 IL MI
1523 04/01/2002 <null> MN
Original table records the transfer activity of assets.
The current location of asset 1522 is NV and 1523 is MI.
The qry result I need is:
AssetID Current Loc
1522 10/01/2002 TX NV
1523 06/01/2002 IL MI
Just the current location of each asset based on the
latest transfer date.
Here's what I attempted:
A sorting select query:
QrySortAssets
AssetID TsfrDate From To
ASC DESC
Then a "Group By" qry based on QrySortAssets using the
function "Last of" or "First of" to grab the first row
of each assetid grouped on from the sorted query.
However, it seems to pay no attention to the sorting of the second query, and is still looking at the original tables order....
Any ideas? thanks!
nstouffer