DKrzemien

I have resolved my issues with the code below: SELECT DISTINCTA1.assetid,A1.agencygroup,A1.agencyname,A1.agencyid,A1.categoryid ServiceCodeID,A1.assignedto,A1.vehicleid,A1.aglocation,A1.description,A1.modelnumber,A1.statusid assetStatusID,A1.status,Rebanded.projectid,Rebanded.statusid,Rebanded.status woStatus,Rebanded.wotype FROM mcm."asset_v" A1 LEFT JOIN (SELECTA2.assetid,A2.agencygroup,A2.agencyname,A2.agencyid,A2.categoryid ServiceCodeID,A2.assignedto,A2.vehicleid,A2.aglocation,A2.description,A2.modelnumber,A2.statusid assetStatusID,A2.status,W1.projectid,W1.statusid,W1.status woStatus,W1.wotype FROM mcm."asset_v" A2 LEFT JOIN mcm."workorderall_v" W1 ON A2.assetid = W1.assetid WHERE (((A2.categoryid)IN (21,22,24)) AND ((W1.projectid)= 27))) AS Rebanded ON A1.assetid = Rebanded.assetid WHERE (((Rebanded.assetid) IS NULL) AND ((A1.categoryid) IN (21,22,24)))ORDER BY A1.agencygroup, A1.agencyname, A1.agencygroup; The query returns All records that have not been assigned a ProjectID=27

I have it as r1.assetid above. I was trying to use alias' because of the multiple use of the asset_v table. I've not nested queries before so I'm not quite sure of the accuracy of the syntax.

Hello, it is Supposed to return All records that have never had a ProjectID = 27. I cannot get it to work. I was hoping another set of eyes might see some problem with the code.

Greetings, I've used sql to a moderate degree within vba and some third-party software (i.e. ReportWriter) and for the most part I do ok. I currently have a report I am trying to generate with an underlying sql query and I just can't get it to work. I was able to mimic what I needed in MS Access but there are some syntax and formatting that I have not used before. I've looked through the examples but I'm missing something. The report will displays radio assets that have Not been assigned a workorder ticket for a current project that is underway. Particulars:- ProjectID=27 if the asset has/had a workorder created for the project. NULL otherwise.- CategoryID=21, 22 or 24 if the radio asset can be assigned the ProjectID (21=Portable Radio, 22=Mobile Radio, 24=Control Station) * I've created a report that shows all the assets that have been assigned the ProjectID=27 which was easy enough but I have to be able to query a view table that has both Current and History records - with that said, you can possibly have the same AssetID multiple times. What I've tried to do is perform a query that will1. Pull All AssetID's with CategoryID in(21,22,24) from ASSET_V table.2. Left Join the Asset_V table (assetid) with WorkOrderAll_V table (assetid) / Where ProjectID=27 AND with CategoryID in(21,22,24)3. Left Join both (1 & 2) Where WorkOrderAll_V.assetid Is Null. I have two tables with the fields below: Asset_V-----------asset_v.assetidasset_v.serialnumberasset_v.categoryasset_v.agencygroupasset_v.agencynameasset_v.agencyidasset_v.categoryid ServiceCodeIDasset_v.assignedtoasset_v.vehicleidasset_v.aglocationasset_v.descriptionasset_v.modelnumberasset_v.statusid assetStatusIDasset_v.status WorkOrderAll_V--------------------workorderall_v.assetidworkorderall_v.projectidworkorderall_v.statusidworkorderall_v.status woStatusworkorderall_v.wotype SELECT DISTINCTROWasset_v.assetid,asset_v.serialnumber,asset_v.category,asset_v.agencygroup,asset_v.agencyname,asset_v.agencyid,asset_v.categoryid ServiceCodeID,asset_v.assignedto,asset_v.vehicleid,asset_v.aglocation,asset_v.description,asset_v.modelnumber,asset_v.statusid assetStatusID,asset_v.status,r1.projectid,r1.statusid,r1.status woStatus,r1.wotypeFROM (asset_vLEFTJOIN (SELECT DISTINCTROW a1.ASSETID, a1.CATEGORYID ServiceCodeID FROM asset_v as a1) )LEFTJOIN (SELECT r1.assetid, r1.projectid FROM workorderall_v as r1) ON a1.assetid = r1.assetid WHERE (((a1.CATEGORYID) In (21,22,24)) AND ((r1.projectid)=27)) WHERE (((a1.ServiceCodeID) in (21,22,24)) AND ((r1.assetid) IS NULL))