where the field Cle2 is a field that identifies the records in the table which owns to the same group
Mont is the field that stores the amount per record
Lettrage is a fileld that stores a label that will change according to the resut of my query.
what I have to do is basically group the table ECD by Cle2 and in the meanwhile sum the values of Mont (Sum(Mont)) as Sum than check if the values in Mont summed and Grouped by cle2 are equal to 0 or not;
if they are =0 than I update the fields Lettrage labeling those records as OK if not then I label those fields as sUSPENS!
I've already done this in access but the code is not so efficient
what i basically do is :
1) create a new table colled provisional
2) insert in this table the values of the select Query
" Insert into Provisor SELECT Cle2 As Cle2p,Sum(MONTANT_ORACLE) AS Sum FROM ECD GROUP BY Cle2"
3) create a table ECD2 empty with the same fields than ECD
4)insert in these table the rusult of the joinin statement like these:
insert INTO ECDlet1 SELECT Provisor.lettrage As Let2,ecd.*FROM Provisor LEFT JOIN ECD ON Provisor.[Cle2p]=ECD.Cle2
5) than clean the db and update the fileds Lettrage with the right label
so what i'd like to do is do something like this without creating all those stupid physical tables
I want to underline that I execute the query in the Visual Basic by using The CurrentDb.Execute statement.
Thank you in advance for your answers!