AboutSyed Rizwan Muhammad Rizvi Expertise I can answers questions regarding web based and desktop based programming in VB.Net. Which can include SOAP, XML, Custom Controls, COM Interoperability etc.
Experience Have been working in this specific area for last 2 years previously I was a VB 6 Developer with experties in other languages as well. Total 10 years of programming experience.
Question i don't know if this is still VB.NET or sql but here it goes:
myd = .runQuery("SELECT tbltrcclient.ClientID, tbltrcservice.ServiceID, tbltrcservice.`Date`, tbltrcclient.Lastname, tbltrcclient.Firstname, tbltrcclient.Barangay, tbltrcservice.`Sitio/Purok`, tbltrcservice.Barangay, tbltrcservice.NatureofService, if (Free = 1 , 'Free', '') as 'Service', tbltrcservice.Remarks, tbltrcservice.NoOfHectares, tbltrcservice.NoOfLiters, tbltrcservice.TotalAmount, tbltrcservice.AmountRemaining, (SELECT IFNULL(SUM(AmountPaid),0) FROM `tbltrcpayment` WHERE ServiceId=tbltrcservice.ServiceId AND ClientId=tbltrcclient.clientId) AmountPaid FROM tbltrcclient INNER JOIN tbltrcservice ON tbltrcclient.ClientID = tbltrcservice.ClientID ")
this is my select code and as you can see my AmountPaid field will automatically sum all that is entered in its field. my problem is i want my AmountRemaining field to give the difference between TotalAmount and AmountPaid automatically also. how will i do that?
Answer myd = .runQuery("SELECT tbltrcclient.ClientID, tbltrcservice.ServiceID, tbltrcservice.`Date`, tbltrcclient.Lastname, tbltrcclient.Firstname, tbltrcclient.Barangay, tbltrcservice.`Sitio/Purok`, tbltrcservice.Barangay, tbltrcservice.NatureofService, if (Free = 1 , 'Free', '') as 'Service', tbltrcservice.Remarks, tbltrcservice.NoOfHectares, tbltrcservice.NoOfLiters, tbltrcservice.TotalAmount, tbltrcservice.AmountRemaining, (SELECT IFNULL(SUM(AmountPaid),0) FROM `tbltrcpayment` WHERE ServiceId=tbltrcservice.ServiceId AND ClientId=tbltrcclient.clientId) AmountPaid, (TotalAmount-AmountPaid) AmountRemaining FROM tbltrcclient INNER JOIN tbltrcservice ON tbltrcclient.ClientID = tbltrcservice.ClientID ")
