How to get field value from last related record?

[Garu]

Hi,

I'm building a solution that tracks movement of items from one location to
another. I have the following tables:

1)Item
ItemNo (related to Movement.ItemNo one-to-many)
Description
Remarks

2)Movement
ItemNo
MovementDate
Location

While browsing Item records in a form, I would like to be able to display
the last "location" where the item has last moved. Is that possible and
how?

Thanks in advance,
Garu

 

[BruceM]

You can build a form based on Item (or better, a query based on Item), with
a subform (single form view) based on a query based on Movement. ItemNo is
the Link Parent/Link Child property for the subform control (the "box"
containing the subform). Sort the query by MovementDate descending. Each
time you go to a new Item record you will see the latest Movement record for
that item.
I can provide some more details about how to go about this, if needed.

 

[John Spencer]

If you want a query that shows that information, the SQL would look like the
following. This query would NOT be updatable.

SELECT Item.*, Movement.Location, Movement.MovementDate
FROM Item INNER JOIN Movement
ON Item.ItemNo = Movement.ItemNo
WHERE MovementDate in
(SELECT TOP 1 MovementDate
FROM Item as Temp
WHERE Temp.ItemNo = Item.ItemNo ORDER BY Temp.ItemDate DESC)

An alternative would be to use something like the following if and Item might
have no movement records
SELECT Item*, LastMove.MovementDate, LastMove.Location
FROM Item LEFT JOIN
(SELECT *
FROM Movement INNER JOIN
(SELECT ItemNo, Max(MovementDate) as LastDate
FROM Movement
GROUP BY ItemNO) as TheLast
ON Movement.ItemNo = TheLast.ItemNo
AND Movement.MovementDate = TheLast.LastDate) as LastMove
ON Item.ItemNo = LastMove.ItemNo

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Garu]

Thanks a lot.
Garu