it is referring to not finding this list1
WHERE (list1.Rev)=(select max(REV) from list1 as l1 where l1.[OwnerDocumentNo]=list1.[OwnerDocumentNo])
you have define list1 in your main query so you would need to define it again in your subquery which is effectively treated as a separate query.
Suggest put your list1 in a separate query and then reference it in this one
e.g. query list1
Code:
SELECT [tblDocument-VP].[Project No], [tblDocument-VP].[Document Type], [tblDocument-VP].Zone, [tblDocument-VP].Discipline, [tblDocument-VP].[PO NO], [tblDocument-VP].[Vendor Name], tblVendorName.VendorDesc, [tblDocument-VP].[Tag No], [tblDocument-VP].OwnerDocumentNo, [tblDocument-VP].[DOCUMENT / DRAWING TITLE], [tbltransmittals-VP].[VEDOR TRANSMITTAL NO], [tbltransmittals-VP].Rev, [tbltransmittals-VP].[Doc STATUS], [tbltransmittals-VP].[AC CODE], [tbltransmittals-VP].[MC AC Status], tblAC_Code.[AC Desc], [tbltransmittals-VP].Transmittal_to_Site, [tbltransmittals-VP].Transmittal_to_Site_date, [tbltransmittals-VP].HyperDoc
FROM (tblVendorName RIGHT JOIN [tblDocument-VP] ON tblVendorName.[VENDOR NAME] = [tblDocument-VP].[Vendor Name]) LEFT JOIN (tblAC_Code RIGHT JOIN [tbltransmittals-VP] ON tblAC_Code.[AC Code] = [tbltransmittals-VP].[AC CODE]) ON [tblDocument-VP].OwnerDocumentNo = [tbltransmittals-VP].OwnerDocumentNo
WHERE [tbltransmittals-VP].Transmittal_to_Site IS NOT Null
main query
Code:
SELECT
list1.[Project No],list1.[Document Type],list1.Zone,list1.Discipline,list1.[PO NO],list1.[Vendor Name],list1.VendorDesc,list1.[Tag No],
list1.OwnerDocumentNo,list1.[DOCUMENT / DRAWING TITLE],list1.[VEDOR TRANSMITTAL NO],list1.Rev,list1.[Doc STATUS],
list1.[AC CODE],list1.[MC AC Status],list1.[AC Desc],list1.Transmittal_to_Site,list1.Transmittal_to_Site_date,list1.HyperDoc
FROM list1
WHERE (list1.Rev)=(select max(REV) from list1 as l1 where l1.[OwnerDocumentNo]=list1.[OwnerDocumentNo])
but you will probably find this to be very slow - better to use a join
query list1MaxRev
Code:
select OwnerDocumentNo, max(REV) AS maxRev from list1 GROUP BY OwnerDocumentNo
main query
Code:
SELECT *
FROM List1 INNER JOIN List1MaxRev ON List1.OwnerDocumentNo=list1MaxRev.OwnerDocumentNo AND List1.Rev=List1MaxRev.maxRev