asked on

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"));

deedub84

ASKER

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

Jeffrey Coachman

deedub84,

Contact the support staff:
https://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

Gustav Brock

And no queries exist in Access??

/gustav

Jeffrey Coachman

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Thanks Jeff.
I see that I missed your word "additional" which makes a difference ...

/gustav

Jeffrey Coachman

/gustav,
;)
Happy Holidays!

Jeff

deedub84

ASKER