Too many rows output by query

[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?

 

[John W. Vinson]

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?

Use a Subquery as a criterion to select only the most recent:

SELECT Equipment.LocationCode, Equipment.EquipmentNum, Movements.MoveDate
FROM Equipment INNER JOIN Movements ON
Equipment.EquipmentID = Movements.EquipmentID
WHERE (((Equipment.Disposed)=False))
AND Movements.MoveDate = (SELECT Max(X.MoveDate) FROM Movements AS X
WHERE X.EquipmentID = Movements.EquipmentID)
ORDER BY Equipment.LocationCode, Equipment.EquipmentNum;

BTW, is there a way to add the distinct command from within the design grid
rather than editing the SQL view?

Yes. Right click the background of the table icons and choose Properties, or
select View... Properties; set the Unique Values property to Yes.

 

[Jeff Boyce]

Define "last" ... can we assume you mean "most recent"?

When you are in the design view/grid, you can left-click on the open gray
space in the upper window (where the tables/queries go), select Properties,
and set the Unique Values property to Yes. I believe this is the same as
DISTINCT...

If you want to see the "most recent" date (?[MoveDate]), try a TOTALS query,
grouping by the other fields and using "Maximum" for your [MoveDate] field.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Len B]

Thanks guys. I feel privileged. 2 x MVPs.

Yes Jeff, to me last means most recent.
tblMovements has fields - EquipID, MovedTo, From, ByWho, MoveDate which
allows me to trace the item from locations A to B to C and when. It is
the most recent date that I need in order to know where it is now (for
comparison with Equipment.LocationCode etc).

John, I understand the principle you put forward but I don't understand
your use of X. Can you give me a help topic or key word(s) so I can further
investigate. (I don't understand how X can appear before it is defined
in the As clause.)

Arrrrgh. I had a vague recollection of right clicking the grid and I
had already clicked on the Title bar of the query, the title bar of
each table listed, the left of the row descriptors and in the grid itself.
I didn't think to try the grid's vacant area and so I just edited the SQL.
--
Len
______________________________________________________
remove nothing for valid email address.
| Define "last" ... can we assume you mean "most recent"?
|
| When you are in the design view/grid, you can left-click on the open gray
| space in the upper window (where the tables/queries go), select
Properties,
| and set the Unique Values property to Yes. I believe this is the same as
| DISTINCT...
|
| If you want to see the "most recent" date (?[MoveDate]), try a TOTALS
query,
| grouping by the other fields and using "Maximum" for your [MoveDate]
field.
|
| Good luck!
|
| Jeff Boyce
| Microsoft Office/Access MVP
|
|
| | >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?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
|
|

 

[John W. Vinson]

John, I understand the principle you put forward but I don't understand
your use of X. Can you give me a help topic or key word(s) so I can further
investigate. (I don't understand how X can appear before it is defined
in the As clause.)

It is defined "before" - since (in SQL processing) the FROM clause is parsed
before the SELECT clause. Since you're using the same table in two different
contexts (in the outer query to get the data, in the inner query to select the
most recent date) it's necessary to use an alias; I just used AS X.

 

[Len B]

Thanks John. Perfectly clear now. Obvious in 20/20 hindsight.

Thanks again.
--
Len
______________________________________________________
remove nothing for valid email address.
| On Fri, 11 Sep 2009 11:27:57 +1000, "Len B" <[email protected]>
| wrote:
|
| >John, I understand the principle you put forward but I don't understand
| >your use of X. Can you give me a help topic or key word(s) so I can
further
| >investigate. (I don't understand how X can appear before it is defined
| >in the As clause.)
| >
|
| It is defined "before" - since (in SQL processing) the FROM clause is
parsed
| before the SELECT clause. Since you're using the same table in two
different
| contexts (in the outer query to get the data, in the inner query to
select the
| most recent date) it's necessary to use an alias; I just used AS X.
| --
|
| John W. Vinson [MVP]