Using a Query i want to check and don't want to show in listbox

[sireesha]

IN Table1----sale_amt and in table2-----collection_amt.group by sale_id i want to sum collection_amts.

i got a solution for this.

This Query solution is assigned to listbox.

but i want in the same query ---------if sumofcollection_amts is equal to sale_amt. that record don't show in the listbox. if those are unequal only shown in listbox . HOw?

is it possible in the same query.

or

i tried one more method. code written in form_load
for i=0 to listbox.listcount
if listbox.column(6,i) ie.,sale_amt=listbox.column(7,i) i.e., sumofcollectionamt then


listbox.removeitem i
end if

but it gives error . listbox rowsoucetype is changed to valuelist. when i change also not working how?


Please give soultion as early as possible. which is possible first method or second method

[June7]

Maybe with subquery in the combobox RowSource query.

Review http://allenbrowne.com/subquery-01.html
especially the example for Aggregation: Counts and totals

[sireesha]

SELECT DISTINCTROW a.M_NUM, a.V_NUM, a.SP_ID, a.Pd, b.Sold, b.S_ID, b.SNAME, b.Sale_AMT, Sum(c..COLLECTION_AMT) AS SumOfCOLLECTION_AMT
FROM (a INNER JOIN b ON (a.SP_ID = b.SP_ID) AND (a.P_ID = b.P_ID)) INNER JOIN c ON b.S_ID = c.S_ID
GROUP BY a.m_NUM, a.V_NuM, a.s_ID, a.PD, b.Sold, b.S_ID, b.SNAME, b.SALE_AMT
HAVING (((b.Sold)=Yes));


where i can use sale_amt is not equal to sumofcollectionamt. sale_amt is in b table, sumofcollectionamt is done inthis query only i want to check how Please help me

[June7]

Did you review the referenced link? The sumofcollectionamt would probably by the nested subquery. I can't be more specific without knowing your data structure and being able to test query. If you want to provide db for analysis, follow instructions at bottom of my post.

[sireesha]

Thanks I got the solution for the above.