SQL Help

I need help creating a query. I'm trying to show only the largest Revision number for each "trimmed" RecordName (the last bit of each Record Name is Rev. 1.0, 1.1, etc- hence the trimming).



Using this query as "Query2":

Code:

---

SELECT tbl_Records.RecordName, (Left(tbl_Records.RecordName,17)) AS TrimmedName, tbl_Records.Revision

FROM tbl_Records;

---

And this as "Query3" works great.

Code:

---

SELECT Query2.RecordName, Query2.TrimmedName, Query2.Revision

FROM Query2

INNER JOIN

(SELECT TrimmedName, Max(Revision) As MaxRev

FROM Query2

GROUP BY TrimmedName) As Max

ON Max.TrimmedName = Query2.TrimmedName

AND Max.MaxRev = Query2.Revision

---

However, I'm trying to write this to further cut-down another query that builds itself via VBA inputs from multiple comboboxes. As such, I'm trying to figure out how to properly write this without having to rely on a saved query. For some reason, the following does not work:

Code:

---

SELECT tbl_Records.RecordName, (Left(tbl_Records.RecordName,17)) AS TrimmedName, tbl_Records.Revision

FROM tbl_Records

INNER JOIN

(SELECT TrimmedName, Max(Revision) As MaxRev

FROM tbl_Records

GROUP BY TrimmedName) As Max

ON Max.TrimmedName = tbl_Records.TrimmedName

AND Max.MaxRev = tbl_Records.Revision

---

The error says, "The specified field 'tbl_Records.TrimmedName' could refer to more than one table listed in the FROM clause of your SQL statement. Thanks in advance!

SQL Help