Jump to content

DKrzemien

Members
  • Content Count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About DKrzemien

  • Rank
    Newbie
  • Birthday 10/10/1964

Previous Fields

  • Languages
    VBA, SQL, VB

Profile Information

  • Location
    Richmond, VA
  • Interests
    SQL, VBA programming
  1. 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
  2. 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.
  3. 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.
  4. 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))
×
×
  • Create New...