Query Help 1

[shannanl]

I am using the following query and it actually displays the data but I now need to combine the like rows such as the two petty cash entries into one entry with a sum of the totals. GL_Accounts_T.EntryID is my key that is common to both. How can I do this?

Thanks in advance - Shannan

1 Petty Cash 10000000 A -241
1 Petty Cash 10000000 A 241
2 Cash On Hand 10100000 A
2 Cash On Hand 10100000 A -1360.46
3 Accounts Payable 20000000 A
4 Revenue From Patient 40000000 A
4 Revenue From Patient 40000000 A -241
5 Bad Debt 61500000 A
5 Bad Debt 61500000 A 76
6 Payroll 61200000 A
6 Payroll 61200000 A 1525.46
7 Accounts Receivable 40000000 A
8 Revenue From Clinic 40000010 A

SELECT DISTINCT
GL_Accounts_T.EntryID, GL_Accounts_T.ActName, GL_Accounts_T.ActNumber, GL_Accounts_T.ActStatus, SUM(GL_Transactions_T.TransAmount1)
AS Balance
FROM GL_Accounts_T LEFT JOIN
GL_Transactions_T ON GL_Accounts_T.EntryID = GL_Transactions_T.GL_Account_1
WHERE (((GL_Accounts_T.ActStatus) = 'A'))
GROUP BY GL_Accounts_T.EntryID, GL_Accounts_T.ActName, GL_Accounts_T.ActNumber, GL_Accounts_T.ActStatus
UNION
SELECT DISTINCT
GL_Accounts_T.EntryID, GL_Accounts_T.ActName, GL_Accounts_T.ActNumber, GL_Accounts_T.ActStatus, SUM(GL_Transactions_T.TransAmount2)
AS Balance
FROM GL_Accounts_T LEFT JOIN
GL_Transactions_T ON GL_Accounts_T.EntryID = GL_Transactions_T.GL_Account_2
WHERE (((GL_Accounts_T.ActStatus) = 'A'))
GROUP BY GL_Accounts_T.EntryID, GL_Accounts_T.ActName, GL_Accounts_T.ActNumber, GL_Accounts_T.ActStatus

 

[r937]

SELECT GL_Accounts_T.EntryID
     , GL_Accounts_T.ActName
     , GL_Accounts_T.ActNumber
     , GL_Accounts_T.ActStatus
     , Trn.Balance
  FROM GL_Accounts_T 
LEFT 
  JOIN (
       SELECT Acct
            , SUM(Bal) as Balance
         FROM (  
              SELECT GL_Account_1       AS Acct
                   , SUM(TransAmount1)  AS Bal
                FROM GL_Transactions_T 
              GROUP
                  BY GL_Account_1
              UNION ALL
              SELECT GL_Account_2       AS Acct
                   , SUM(TransAmount2)  AS Bal
                FROM GL_Transactions_T 
              GROUP
                  BY GL_Account_2
              ) AS T1T2
       GROUP
           BY Acct
       ) AS Trn 
    ON Trn.Acct = GL_Accounts_T.EntryID
 WHERE GL_Accounts_T.ActStatus = 'A'

r937.com | rudy.ca

 

[shannanl]

Thank you. That worked great.