Total Query

[SHAWTY721]

I have a query that produces this output and I want to be able to get the sum of a field based on the Account & Subaccount matching. Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

I would like to have the result look like this in my spreadsheet
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

So is there a way to get a total within a total query I guess my question is.

Thanks in advance!

 

[lespaul]

do you need the check date? I would change your original query by removing CheckDate from the SELECT and GROUP BY clause and that will get you the result you want.....

Otherwise, create a second query (that basically removes the CheckDate from the SELECT and GROUP BY):

SELECT Account, SubAccount, AcctDesc, SUM(SumOfGross) FROM 1stQuery GROUP BY Account, SubAccount, AcctDesc

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[lameid]

Leslie,

FYI...

thread705-1420958

Check out the 6 Nov 07 10:29 ECT post and down.

 

[SHAWTY721]

I have a query that produces this output and I want to be able to get the sum of a field based on the Account & Subaccount matching. Here is what the query result looks like.


This is the results that appear on the excel spreadsheet:
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1684
60810 0900 ...... 1687

This is the result of the query that produces the outcome above:
Account Subaccount AcctDesc Gross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

 

[lameid]

SHAWTY721,

It is the same soultion and question.

 

[SHAWTY721]

The sum of the gross field 842 + 843.5 should be 1685.5 but instead it comes up with two different calculations 1684 or 1687.

 

[PHV]

Any chance you could post YOUR sql codes ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

His original SQL from a procedure:

    sSQL = "SELECT E.GLDEPT, " & _
                  "GL.GL_Acct, " & _
                  "GL.GL_Subacct, " & _
                  "GL.GL_Dept, " & _
                  "GL.AccountDescription , " & _
                  "ADP.BranchNumber, " & _
                  "Sum(E.GROSS) As TheGross " & _
            "FROM tblAllADPCoCodes As ADP, " & _
                 "tblGLAllCodes As GL INNER JOIN tblAllPerPayPeriodEarnings As E ON " & _
                        "GL.Dept = E.GLDEPT " & _
            "GROUP BY E.GLDEPT, " & _
                     "GL.GL_Acct, " & _
                     "GL.GL_Subacct, " & _
                     "GL.GL_Dept, " & _
                     "GL.AccountDescription, " & _
                     "E.PG, " & _
                     "E.[LOCATION#], " & _
                     "ADP.BranchNumber, " & _
                     "E.CHECK_DT " & _
            "HAVING PG = '" & strADPCompany & "' AND " & _
                   "E.[LOCATION#] = '" & strLocationNo & "' AND " & _
                   "ADP.BranchNumber = " & lBranchNo & " AND " & _
                   "E.CHECK_DT Between #" & dtFrom & "# AND #" & dtTo & "#;"

For the same question I had recommend moving the having to the where.

I am now noticing the lack of an ANSI 92 join for tblAllADPCoCodes. Assuming the logic is right, the only thing to do is group by the results of this query (one of lespauls recomendations).

Is the purpose to use the query for your VBA code or just a query?

A way that should work in either case:

Select A.GL_Acct, A.GL_Subacct, A.AccountDescription, Sum(TheGross)
From (<Existing Select Here>) As A
Group By A.GL_Acct, A.GL_Subacct, A.AccountDescription

Note <Existing Select Here> should be replaced with the entire SQL statment of the existing query. Also you can alias the fields as you see fit. Also in code you obviously have to concatenate the string appropriately.