grouping

[Gus Chuch]

Help!
I just don’t understand why I can’t get this SQL to work the way I would
like it.
I got 2 tables I’m working with.
tblEquipment which is a record of all my Equipment.
And tblEquipmentRotation which keeps a history of all the places the
equipment has been. The following SQL works fine it will give me the last
date of where the equipment has been which is what I’m looking for.

SELECT tblEquipment.EquipmentID, tblEquipment.Description,
Max(tblEquipmentRotation.Date_Installed) AS MaxOfDate_Installed
FROM tblEquipment LEFT JOIN tblEquipmentRotation ON tblEquipment.EquipmentID
= tblEquipmentRotation.EquipmentID
GROUP BY tblEquipment.EquipmentID, tblEquipment.Description;

But as soon as I add the LocationID it stops grouping the EquipmentID and I
get all the records how can I add the field LocationID with out grouping the
LoctionID

SELECT tblEquipment.EquipmentID, tblEquipment.Description,
Max(tblEquipmentRotation.Date_Installed) AS MaxOfDate_Installed,
tblEquipmentRotation.LocationID
FROM tblEquipment LEFT JOIN tblEquipmentRotation ON tblEquipment.EquipmentID
= tblEquipmentRotation.EquipmentID
GROUP BY tblEquipment.EquipmentID, tblEquipment.Description,
tblEquipmentRotation.LocationID;


OR - Is their a way to have a expr field in my query that would use the
dlookup with a criteria that use 2 (two) fields to find the LocationID ?
something like =Dlookup("[LocationID]â€, “tblEquipmentRotationâ€, “[EquipmentId
& MaxOfDate_Installed]â€)

 

[ChrisJ]

I would guess that you are wanting to get a list of each item of equipment
showing the date it was last used and the location at which it was used on
that date?

While it may be possible to do this in a single query I would be tempted to
use two.

The first query is your first example that works. Call this qry1.

Then the second query would look something like...

SELECT qry1.EquipmentID, qry1.Description,
qry1MaxOfDate_Installed,
tblEquipmentRotation.LocationID
FROM qry1 INNER JOIN tblEquipmentRotation ON qry1.EquipmentID =
tblEquipmentRotation.EquipmentID
AND qry1.qry1MaxOfDate_Installed = tblEquipmentRotation.Date_Installed;