Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: Select Group query without create physical table

    Hi everybody
    I have a table named ECD like this:

    Code:
    Cle2                 Mont           Lettrage
    acbd....            +8,36            Suspens    
    abcd...             -8,36            Suspens
    dced..              +12,89          Suspens
    dced..              -12,89           Suspens
    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
    Code:
    " 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:
    Code:
    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!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically, you need to queries.

    1. For updating [Lettrage] to 'OK':
    Code:
    UPDATE ECD 
    SET ECD.Lettrage = 'OK'
    WHERE ECD.Cle2 IN ( SELECT ECD.Cle2
                        FROM ECD
                        GROUP BY ECD.Cle2
                        HAVING (Sum(ECD.Mont)=0)
                      );
    2. For updating [Lettrage] to 'Suspens':
    Code:
    UPDATE ECD 
    SET ECD.Lettrage = 'Suspens'
    WHERE ECD.Cle2 NOT IN ( SELECT ECD.Cle2
                            FROM ECD
                            GROUP BY ECD.Cle2
                            HAVING (Sum(ECD.Mont)=0)
                          );
    Last edited by Sinndho; 05-02-12 at 08:11. Reason: Corrected the second query.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    Thanks!!!!! i'll test these suggestions as soon as possible!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •