I wrote following query where i summed a field billed amount as amount, when i am trying to use this alias in other calculation, query returns error " Invalid column name amount". How i can re-use field alias name in sql query.
second, as u see in my query that i added a lot fields in group, because i have in need individual information of each job and simultaneously ccumulated information of each job, no doubt i got the desired result but i am not satisfy, my suspect is, this junk of fields in group my slow down the query result, please check out this query and suggest what should be the possible smart query for same desired result
.Open "select b.trdate,e.jobno,e.jobdesc,p.partyname,b.manualbillid as BillNo , " & _
"sum(b.billedqty) as Quantity,b.billedrate as Rate,round(sum(b.billedamount),0) as Amount, " & _
"round(amount*15/100,'0') as GSt,round(sum(b.billedamount*15/100),0) + round(sum(b.billedamount),0) as TotValue " & _
"FROM enquiry e,parties p, bills b " & _
" " & FilterText & " " & _
"GROUP BY b.trdate,e.jobno,e.jobdesc,p.partyname,b.manualbillid, " & _
"b.billedrate order by p.partyname,b.manualbillid,b.trdate "