L
Len B
I have two Tables called 'Equipment' and 'Movements' Joined on the primary
key of Equipment. There are many movements per item of Equip.
Here is the (trimmed) Select clause I have.
SELECT Equipment.LocationCode, Equipment.EquipmentNum, Movements.MoveDate
FROM Equipment INNER JOIN Movements ON
Equipment.EquipmentID = Movements.EquipmentID
WHERE (((Equipment.Disposed)=False))
ORDER BY Equipment.LocationCode, Equipment.EquipmentNum;
This gives every movement of every item of equipment.
I am only interested in the most recent move. (From locationA to locationB
on date).
Adding DISTINCT or DISTINCTROW does not reduce the number of rows of
output.
How should I change the code to ensure only the latest movement record is
joined to the equipment record?
BTW, is there a way to add the distinct command from within the design grid
rather than editing the SQL view?
key of Equipment. There are many movements per item of Equip.
Here is the (trimmed) Select clause I have.
SELECT Equipment.LocationCode, Equipment.EquipmentNum, Movements.MoveDate
FROM Equipment INNER JOIN Movements ON
Equipment.EquipmentID = Movements.EquipmentID
WHERE (((Equipment.Disposed)=False))
ORDER BY Equipment.LocationCode, Equipment.EquipmentNum;
This gives every movement of every item of equipment.
I am only interested in the most recent move. (From locationA to locationB
on date).
Adding DISTINCT or DISTINCTROW does not reduce the number of rows of
output.
How should I change the code to ensure only the latest movement record is
joined to the equipment record?
BTW, is there a way to add the distinct command from within the design grid
rather than editing the SQL view?