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"));
Start Free Trial