If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to get field value from last related record?
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 |
#2
|
|||
|
|||
How to get field value from last related record?
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. "Garu" wrote in message ... 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 |
#3
|
|||
|
|||
How to get field value from last related record?
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 wrote: 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 |
#4
|
|||
|
|||
How to get field value from last related record?
Thanks a lot.
Garu "Garu" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|