I am trying to add the following field into allwo.rpt (workorders report)
probstat.notes
I have successfully added other fields such as acctnumber, however this is different. It is refering to the probstat table.
Can someone please show me how to customise the sql for this report to include the probstat.notes field.
I have tried many things, however the sql code is confusing me.
here is the sql for your convenience.
SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, “Work required: " & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & “Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (” & [unitmodels].[model] & “) SN: " & [units].[sn],””) AS probheader, “test” AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, probstat.notes???, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.phone3, clients.email, clients.company AS clientname, clients.acctnumber, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY
FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT “1” AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, “Service: " & users.first & " " & users.last & " - " & IIf(labor.hours>0,labor.hours & " @ " & Format(rates.rate,“Currency”) & " (pn:” & rates.partnum & “)”,"") & IIf(labor.nchours>0," (" & labor.nchours & " no charge)","") & IIf(labor.travhours>0,", " & labor.travhours & " travel @ " & Format(travelrates.rate,“Currency”) & IIf(IsNull(travelrates.partnum),""," (pn:" & travelrates.partnum & “)”),"") AS item, (labor.hoursrates.rate)+(labor.travhourstravelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS
FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) UNION ALL (SELECT “2” AS ctype, “na” AS LABPN, “na” AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, "Parts: " & IIf(IsNull(woparts.misc), parts.partnumber & " " & parts.description & “: " & woparts.quantity & " @ " & Format(woparts.price,“Currency”) & " ea.(sn:” & woparts.sn & “)”,woparts.misc & “: " & woparts.quantity & " @ " & Format(woparts.price,“Currency”)& " ea.(sn:” & woparts.sn & “)”) AS item, woparts.quantity*woparts.price AS linetotal, “” AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS
FROM (probs INNER JOIN woparts ON probs.id = woparts.link) LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) UNION ALL (SELECT “3” AS ctype,“na” AS LABPN, “na” AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, "Third party service: " AS item, subrepair.charge AS linettotal, “” AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS
FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id =