none
Create a statement of account based on 2 queries RRS feed

  • Question

  • Good day,

    I have 2 queries that gives me the total deposits for a customer and the total purchases, I would like to generate a statement of account for my customers like this (An image worth a thousand words)

    Thank you for your time

    Claude


    Claude Larocque

    Tuesday, December 6, 2016 7:54 AM

Answers

  • I have 2 queries that gives me the total deposits for a customer and the total purchases, I would like to generate a statement of account for my customers like this (An image worth a thousand words)

    Hi Claude,

    As a customer can have deposits and no purchase, but also purchases with no deposits, I would use tblCustomers as the starting point, and use Left joins.

    sql_string = "SELECT * FROM ((tblCustomers" _

                    & " LEFT JOIN tblInterneDepots ON tblInternetDepots.IDDetenteur = tblCustomers.IDDetenteur)" _

                    & " LEFT JOIN tblAchatsDetenteurs ON tblAchatsDetenteurs.IDDetenteur = tblCurtomers.IDDetenteur)" _

                    & " WHERE " & <some conditions to select the active customers>

    I have simplified the French names for easier typing.

    Instead of the "*" you can specify the field you want to retrieve, such as:

        SommeDeTot, SommeDeMontantRecu, (SommeDeTot - SommeDeMontantRecu) AS Balance, ...

    Probably you have to account for Null-values in SommeDeTot and SommeDeMontantRecu by converting a Null-value to 0 using the Nz-function.

    Imb.


    • Edited by Imb-hb Tuesday, December 6, 2016 8:36 AM added: Probably ...
    • Marked as answer by Claude Larocque Tuesday, December 6, 2016 9:58 PM
    Tuesday, December 6, 2016 8:33 AM
  • Try this:

     

    SELECT id_carte_repas, ClasserSousDétenteur,

    SUM(SommeDeTot) AS [Total des Dépôts],

    SUM(SommeDeMontantReçu) AS [Total  des Achats]

    SUM(SommeDeTot) - SUM(SommeDeMontantReçu) AS Balance

    FROM

        (SELECT id_carte_repas, ClasserSousDétenteur, SommeDeTot,

          0 AS SommeDeMontantReçu

          FROM [tblInternetDépôts pour rapport totaux)

          UNION ALL

          SELECT id_carte_repas, ClasserSousDétenteur, 0,  SommeDeMontantReçu

          FROM [tblAchatsDétenteurs pour rapport totaux]) AS DépôtsEtAchats

    GROUP BY id_carte_repas, ClasserSousDétenteur;

     

    BTW, an image might be worth a thousand words, but it’s a PITA when it's necessary to copy the object names.  The SQL statements of each query would have helped, as it would then simply be a matter of copying and pasting.


    Ken Sheridan, Stafford, England

    Tuesday, December 6, 2016 5:09 PM

All replies

  • I have 2 queries that gives me the total deposits for a customer and the total purchases, I would like to generate a statement of account for my customers like this (An image worth a thousand words)

    Hi Claude,

    As a customer can have deposits and no purchase, but also purchases with no deposits, I would use tblCustomers as the starting point, and use Left joins.

    sql_string = "SELECT * FROM ((tblCustomers" _

                    & " LEFT JOIN tblInterneDepots ON tblInternetDepots.IDDetenteur = tblCustomers.IDDetenteur)" _

                    & " LEFT JOIN tblAchatsDetenteurs ON tblAchatsDetenteurs.IDDetenteur = tblCurtomers.IDDetenteur)" _

                    & " WHERE " & <some conditions to select the active customers>

    I have simplified the French names for easier typing.

    Instead of the "*" you can specify the field you want to retrieve, such as:

        SommeDeTot, SommeDeMontantRecu, (SommeDeTot - SommeDeMontantRecu) AS Balance, ...

    Probably you have to account for Null-values in SommeDeTot and SommeDeMontantRecu by converting a Null-value to 0 using the Nz-function.

    Imb.


    • Edited by Imb-hb Tuesday, December 6, 2016 8:36 AM added: Probably ...
    • Marked as answer by Claude Larocque Tuesday, December 6, 2016 9:58 PM
    Tuesday, December 6, 2016 8:33 AM
  • Try this:

     

    SELECT id_carte_repas, ClasserSousDétenteur,

    SUM(SommeDeTot) AS [Total des Dépôts],

    SUM(SommeDeMontantReçu) AS [Total  des Achats]

    SUM(SommeDeTot) - SUM(SommeDeMontantReçu) AS Balance

    FROM

        (SELECT id_carte_repas, ClasserSousDétenteur, SommeDeTot,

          0 AS SommeDeMontantReçu

          FROM [tblInternetDépôts pour rapport totaux)

          UNION ALL

          SELECT id_carte_repas, ClasserSousDétenteur, 0,  SommeDeMontantReçu

          FROM [tblAchatsDétenteurs pour rapport totaux]) AS DépôtsEtAchats

    GROUP BY id_carte_repas, ClasserSousDétenteur;

     

    BTW, an image might be worth a thousand words, but it’s a PITA when it's necessary to copy the object names.  The SQL statements of each query would have helped, as it would then simply be a matter of copying and pasting.


    Ken Sheridan, Stafford, England

    Tuesday, December 6, 2016 5:09 PM
  • Thanks Imb and Ken, I used the statement of Ken because it was almost perfect, I just change some typo (because of the French language) and it works perfectly

    Here is the final code in case someone else needs that:

    Claude

    SELECT DépôtsEtAchats.id_carte_repas, DépôtsEtAchats.ClasserSousDétenteur, Sum(DépôtsEtAchats.SommeDeTotalMontant) AS [Total des Dépôts], Sum(DépôtsEtAchats.SommeDeMontantReçu) AS [Total  des Achats], Sum(SommeDeTotalMontant)-Sum(SommeDeMontantReçu) AS Balance
    FROM (SELECT id_carte_repas, ClasserSousDétenteur, SommeDeTotalMontant, 0 AS SommeDeMontantReçu FROM [tblInternetDépôts pour rapport totaux] UNION ALL SELECT id_carte_repas, ClasserSousDétenteur, 0,  SommeDeMontantReçu FROM [tblAchatsDétenteurs pour rapport totaux])  AS DépôtsEtAchats
    GROUP BY DépôtsEtAchats.id_carte_repas, DépôtsEtAchats.ClasserSousDétenteur;
    


    Claude Larocque

    Tuesday, December 6, 2016 9:56 PM