Multiple-Column Subquery In FROM Clause

[Guest]

Why doesn’t my SQL work correctly?
I have a table tblEquipmentRotation with the following columns HistoryID,
LocationID, EquipmentID & Date_Installed. It keeps a running history of where
things have been.
When I run the Subquery:
(SELECT tblEquipmentRotation.EquipmentID,
Max(tblEquipmentRotation.Date_Installed) AS DateInsatlled
FROM tblEquipmentRotation
GROUP BY EquipmentID)
I get 262 records which is correct. But when I run the following query, with
the subquery in the FROM Clause I get all the record. Why?

SELECT b.LocationID, a.EquipmentID, a.DateInstalled
FROM tblEquipmentRotation AS b, [SELECT tblEquipmentRotation.EquipmentID,
Max(tblEquipmentRotation.Date_Installed) AS DateInstalled
FROM tblEquipmentRotation
GROUP BY EquipmentID]. AS a
WHERE ((.[EquipmentID]=[a].[EquipmentID]));

I have also tried running the subquery in the WHERE Clause but all it does
is shut down Access. Any Ideas it should be a text book query.

 

[Granny Spitz via AccessMonster.com]

I have also tried running the subquery in the WHERE Clause but all it does
is shut down Access.

I'll bet it does!

Any Ideas it should be a text book query.

You're using a Cartesian Join. You need to use ANSI SQL syntax for the
inline subquery:

SELECT *
FROM TableA INNER JOIN (SELECT * FROM TableB WHERE X = 1) AS B ON TableA.ID =
B.ID;

 

[Guest]

--
thank You

I'll bet it does!


You're using a Cartesian Join. You need to use ANSI SQL syntax for the
inline subquery:

SELECT *
FROM TableA INNER JOIN (SELECT * FROM TableB WHERE X = 1) AS B ON TableA.ID =
B.ID;

--
Message posted via AccessMonster.com


So my SQL should look like this, Right? Because I still get all the records from the table. It should limit it to the Subquery only (262 records).

SELECT b.LocationID, a.EquipmentID, a.DateInsatlled
FROM tblEquipmentRotation AS b INNER JOIN (SELECT
tblEquipmentRotation.EquipmentID, Max(tblEquipmentRotation.Date_Installed) AS
DateInsatlled
FROM tblEquipmentRotation
GROUP BY EquipmentID) AS a ON b.EquipmentID = a.EquipmentID;