• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Elegant subquery to sum accounting transaction file

My accounting system is based in SQL Server.  I use Access 2003 as a front-end reporting and data manipulation  tool as I am much more familiar with that than SQL Server.

The accounting system maintains a transaction file of General Ledger transactions with separate fields for debits and credts.  It also maintains for reporting purposes an account balance table with balances by fiscal year, account, and subaccount.  This table includes fields for each fiscal year that includes a beginning balance, 12 period activity totals for the net activity for each fiscal year, and for balance sheet accounts (accounts that begin with 1, 2, or 3) 12 Life-to-date totals that equal the net balance of the current and all prior periods.

I need to rebuild the summary table based on the transaction table entries.  I'm having trouble in 2 areas:  how to build the Life-to-date fields (right now I just get the current year's data) and pulling the  Beginning Balance field (the period 12 balance from the prior year).  I attempted to use subqueries, but got a 'non-updateable' type error.

The other main wrinkle is that the fiscal period field is text in the format YYYYMM, so it can't be readily used for finding the prior period or year.

I had planned to create a temporary table, then copy over the data into the live table.

Here is the query as I have it:

INSERT INTO dbo_AcctHist ( Acct, AnnBdgt, AnnMemo1, BalanceType, BdgtRvsnDate, BegBal, CpnyID, Crtd_DateTime, Crtd_Prog, Crtd_User, CuryId, DistType, FiscYr, LastClosePerNbr, LedgerID, LUpd_DateTime, LUpd_Prog, LUpd_User, NoteID, PtdAlloc00, PtdAlloc01, PtdAlloc02, PtdAlloc03, PtdAlloc04, PtdAlloc05, PtdAlloc06, PtdAlloc07, PtdAlloc08, PtdAlloc09, PtdAlloc10, PtdAlloc11, PtdAlloc12, PtdBal00, PtdBal01, PtdBal02, PtdBal03, PtdBal04, PtdBal05, PtdBal06, PtdBal07, PtdBal08, PtdBal09, PtdBal10, PtdBal11, PtdBal12, PtdCon00, PtdCon01, PtdCon02, PtdCon03, PtdCon04, PtdCon05, PtdCon06, PtdCon07, PtdCon08, PtdCon09, PtdCon10, PtdCon11, PtdCon12, S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, SpreadSheetType, Sub, User1, User2, User3, User4, User5, User6, User7, User8, YtdBal00, YtdBal01, YtdBal02, YtdBal03, YtdBal04, YtdBal05, YtdBal06, YtdBal07, YtdBal08, YtdBal09, YtdBal10, YtdBal11, YtdBal12, YTDEstimated )
SELECT dbo_GLTran.Acct, 0 AS AnnBdgt, 0 AS AnnMemo1, "A" AS BalanceType, #11/21/2007# AS BdgtRvsnDate, 0 AS BegBal, dbo_GLTran.CpnyID, #11/21/2007# AS Crtd_DateTime, "Access" AS Crtd_Prog, "DMW" AS Crtd_User, "USD" AS CuryId, "" AS DistType, dbo_GLTran.FiscYr, IIf([fiscyr]="2007","200710",[fiscyr] & "11") AS LastClosePerNbr, dbo_GLTran.LedgerID, #2/18/2006# AS LUpd_DateTime, "Access" AS LUpd_Prog, "DMW" AS LUpd_User, 0 AS NoteID, 0 AS PtdAlloc00, 0 AS PtdAlloc01, 0 AS PtdAlloc02, 0 AS PtdAlloc03, 0 AS PtdAlloc04, 0 AS PtdAlloc05, 0 AS PtdAlloc06, 0 AS PtdAlloc07, 0 AS PtdAlloc08, 0 AS PtdAlloc09, 0 AS PtdAlloc10, 0 AS PtdAlloc11, 0 AS PtdAlloc12, Sum(IIf(Right([perpost],2)='01',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal00, Sum(IIf(Right([perpost],2)='02',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal01, Sum(IIf(Right([perpost],2)='03',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal02, Sum(IIf(Right([perpost],2)='04',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal03, Sum(IIf(Right([perpost],2)='05',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal04, Sum(IIf(Right([perpost],2)='06',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal05, Sum(IIf(Right([perpost],2)='07',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal06, Sum(IIf(Right([perpost],2)='08',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal07, Sum(IIf(Right([perpost],2)='09',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal08, Sum(IIf(Right([perpost],2)='10',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal09, Sum(IIf(Right([perpost],2)='11',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal10, Sum(IIf(Right([perpost],2)='12',IIf([acct] Like '1*' Or [acct] Like '5*' Or [acct] Like '6*',[dramt]-[cramt],[cramt]-[dramt]),0)) AS PtdBal11, Sum(0) AS PtdBal12, 0 AS PtdCon00, 0 AS PtdCon01, 0 AS PtdCon02, 0 AS PtdCon03, 0 AS PtdCon04, 0 AS PtdCon05, 0 AS PtdCon06, 0 AS PtdCon07, 0 AS PtdCon08, 0 AS PtdCon09, 0 AS PtdCon10, 0 AS PtdCon11, 0 AS PtdCon12, "" AS S4Future01, "" AS S4Future02, 0 AS S4Future03, 0 AS S4Future04, 0 AS S4Future05, 0 AS S4Future06, #1/1/1900# AS S4Future07, #1/1/1900# AS S4Future08, 0 AS S4Future09, 0 AS S4Future10, "" AS S4Future11, "" AS S4Future12, "" AS SpreadSheetType, dbo_GLTran.Sub, "" AS User1, "" AS User2, 0 AS User3, 0 AS User4, "" AS User5, "" AS User6, #1/1/1900# AS User7, #1/1/1900# AS User8, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "01",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "01",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "01",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '01' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '01' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal00, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "02",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "02",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "02",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '02' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '02' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal01, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "03",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "03",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "03",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '03' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '03' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal02, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "04",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "04",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "04",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '04' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '04' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal03, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "05",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "05",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "05",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '05' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '05' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal04, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "06",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "06",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "06",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '06' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '06' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal05, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "07",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "07",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "07",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '07' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '07' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal06, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "08",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "08",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "08",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '08' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '08' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal07, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "09",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "09",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "09",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '09' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '09' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal08, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "10",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "10",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "10",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '10' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '10' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal09, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "11",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "11",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "11",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '11' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '11' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal10, Sum(IIf([acct] Like "1*" And [perpost]<=[fiscyr] & "12",[dramt]-[cramt],IIf([acct] Like "2*" And [perpost]<=[fiscyr] & "12",[cramt]-[dramt],IIf([acct] Like "3*" And [perpost]<=[fiscyr] & "12",[cramt]-[dramt],IIf([acct] Like "4*" And [perpost]<=[fiscyr] & '12' And [perpost]>=[fiscyr] & '01',[cramt]-[dramt],IIf([perpost]<=[fiscyr] & '12' And [perpost]>=[fiscyr] & '01',[dramt]-[cramt],0)))))) AS YtdBal11, Sum(0) AS YtdBal12, 0 AS YTDEstimated
FROM dbo_GLTran
GROUP BY dbo_GLTran.Acct, 0, 0, "A", #11/21/2007#, 0, dbo_GLTran.CpnyID, #11/21/2007#, "Access", "DMW", "USD", "", dbo_GLTran.FiscYr, IIf([fiscyr]="2007","200710",[fiscyr] & "11"), dbo_GLTran.LedgerID, #2/18/2006#, "Access", "DMW", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "", "", 0, 0, 0, 0, #1/1/1900#, #1/1/1900#, 0, 0, "", "", "", dbo_GLTran.Sub, "", "", 0, 0, "", "", #1/1/1900#, #1/1/1900#, 0, dbo_GLTran.Posted
HAVING (((dbo_GLTran.Acct)="29021") AND ((dbo_GLTran.Posted)="P"));
0
deedub84
Asked:
deedub84
  • 4
  • 4
  • 2
1 Solution
 
deedub84Author Commented:
Maybe I should simplify my request some:

My gltran file would be like

Acct                Sub                   Period             Dramt             cramt
101                  A100                    200607         1000                  0
101                  A100                   200701         100                      0
101                  A100                    200702         315                     0
101                  B200                    200702         220                      0
101                  B200                     200709           0                     120


I need a query that would create a Life-to-date summary that totals the Dramt - Cramt for the current and all previous periods such as

Acct    Sub     FY   BegBal  LTD01    LTD02   LTD03   LTD04  LTD05  LTD06  LTD07  LTD08  LTD09   LTD10  LTD11 LTD12
101      A100  2006     0           0             0            0            0           0            0        1000    1000     1000       1000       1000     1000
101      A100   2007  1100       1415  
101       B200   2007    0         220    220   220  220   220  220   220  100    100 etc

Thanks,

Derek
0
 
Jeffrey CoachmanMIS LiasonCommented:
deedub84,

Contact the support staff:
http://www.experts-exchange.com/support.jsp
...and ask them to create a link to this Q in the SQL Server Topic Area.

They may be able to provide some additional help, since this appears to be more of a Query question than an Access question.

JeffCoachman
0
 
Gustav BrockCIOCommented:
And no queries exist in Access??

/gustav
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanMIS LiasonCommented:
cactus_data,

Ha Ha!
:)

I was just suggesting the link in order to Maximize the number of Experts who would see this Q.

500Point Q's don't usually make it past the 12 hr mark with no posts!
:)

Oh and by the way...
Happy belated on crossing the 1,000,000 Total point mark!
Looks like you will be crossing that mark in the Access TA very soon as well!
Well deserved!
:)

Jeff
0
 
Gustav BrockCIOCommented:
Here is one method using subqueries:
SELECT 
  tblTrans.Acct, 
  tblTrans.Sub, 
  Left([Period],4) AS FY, 
    CCur(Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)<Left(tblTrans.Period,4)),0)) AS 
  BegBal, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="01"),0) AS 
  LTD01, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="02"),0) AS 
  LTD02, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="03"),0) AS 
  LTD03, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="04"),0) AS 
  LTD04, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="05"),0) AS 
  LTD05, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="06"),0) AS 
  LTD06, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="07"),0) AS 
  LTD07, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="08"),0) AS 
  LTD08, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="09"),0) AS 
  LTD09, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="10"),0) AS 
  LTD10, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="11"),0) AS 
  LTD11, 
    [BegBal]+Nz((Select Sum(DrAmt-CrAmt) From tblTrans As T 
    Where T.Acct=tblTrans.Acct And T.Sub=tblTrans.Sub And 
    Left(T.Period,4)=Left(tblTrans.Period,4) And 
    Right(T.Period,2)<="12"),0) AS 
  LTD12
FROM 
  tblTrans
GROUP BY 
  tblTrans.Acct, 
  tblTrans.Sub, 
  Left([Period],4);

Open in new window

0
 
Gustav BrockCIOCommented:
Thanks Jeff.
I see that I missed your word "additional" which makes a difference ...

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
/gustav,
;)
Happy Holidays!

Jeff
0
 
deedub84Author Commented:
I LOVE the new code window.  Way to go EE!
0
 
Jeffrey CoachmanMIS LiasonCommented:
cactus_data,

I guess we really didn't need the SQL TA, after all!
:)

JeffC

0
 
Gustav BrockCIOCommented:
No, not really. On the other hand, a stored procedure might provide a faster solution if this proves too slow.

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now