Running total problem...

[GIScrew]

Thanks Ido and Brian,
I am still having a problem. I should explain a few factors. I have 4 tables linked by a primary id, the census tract id number. My primary table is the assessment area table, most important, composed of 256 records, ie 256 census tracts for this assessment area, all unique census tract ids. My other tables are loans, denials and deposits. I want to display number of indate loans in one field, sum of the indate loans in the next field, deposits, sum of deposits, all for each census tract id in the assessment area. The problem i am running into is the loans, denials, deposit tables include duplicate census tract ids. I grouped the census ids in for the assessment area. I applied both solutions, running total by count type and the summarizing based on 1 or 0. The results are 1s and 2s for each census tract id. I am a small company looking to go big. I'm close, but where lies the problem?
Thanks,
GIScrew

 

[GIScrew]

I attempted to replicate the report but I didn't have any of the fields sorted.

ID objID DpCount DpBal LnCount ObjID LnBal
102.03 19 1 0 1 23 300
102.03 19 1 0 1 24 400
102.03 19 1 0 1 25 350
102.03 19 1 0 1 26 200
102.03 19 1 0 1 27 500
102.03 19 1 0 1 28 600
102.03 19 1 0 1 29 700

102.03 32 1 100 1 23 300
102.03 32 1 100 1 24 400
102.03 32 1 100 1 25 350
102.03 32 1 100 1 26 200
102.03 32 1 100 1 27 500
102.03 32 1 100 1 28 600
102.03 32 1 100 1 29 700

102.03 45 1 0 1 23 300
102.03 45 1 0 1 24 400
102.03 45 1 0 1 25 350
102.03 45 1 0 1 26 200
102.03 45 1 0 1 27 500
102.03 45 1 0 1 28 600
102.03 45 1 0 1 29 600

-This continues for 133 deposits.

//I didn't make the previous report sample very clear by not sorting the fields. The fields are now sorted like you suggested. Like I posted before, I have 7 loans and 133 deposits for the assessment area id 102.03. The 7 loans repeat for the entire 102.03 until all 133 deposits have been accounted for. I changed around the counts for the deposits and loans but the way the report is displayed now, it the settings are incorrect. Ideas?
-GIScrew

 

[lbass]

Now we're talkin'...

Deposit Count RT:
Select {deposit.objID}
Summary = distinctcount
Evaluate for each record
Reset on change of {assessmentarea.trt_ID}

(Or you could do a count and evaluate on change of field {deposit.objID}).

Deposit Balance RT:
Select {deposits.balance}
Summary = sum
Evaluate on change of {deposit.objID}
Reset on change of {assessmentarea.trt_ID}

Loan Count RT:
Select {loan.objID}
Summary = count or distinctcount
Evaluate for each record
Reset on change of {deposit.objID}

Loan Balance RT:
Select {loan.balance}
Summary = sum
Evaluate for each record
Reset on change of {deposit.objID}

These should all be placed in the group (Assessment Area Tract) footer.

-LB

 

[GIScrew]

I had a small problem with the loan count so I changed the reset on change of (assessmentarea.trt_id}, same as the deposit count. The loan count now functions properly. The deposit balance and loan balance are still not working correctly. Your solutions makes sense now. Placing the fields in the detail section is a good way to understand how to determine settings for the running totals. Census tracts with no deposits or loans, the balances are correct. Here is an exmaple of the running totals.

Id DpCount DpBal LnCount LnBal
102.03 133 1.69m 7 2,361
108 220 20.5m 29 15,521
109.01 38 135,344 1 9,576 (Correct)
109.02 159 4.9m 7 0
109.03 39 497,222 2 17,000 (Correct LnBal)
109.04 15 142,292 2 52,206 (Correct LnBal)
109.06 39 592,911 6 21,534
109.07 32 103,587 2 115,820(Correct LnBal)
109.08 45 38,686 2 6,800 (Correct LnBal)

//(Correct=all dp/ln values are correct)
Some of the loan and deposit balances are correct(noted)but all of the deposit balances are all incorrect. I appreciate your patience and willingness to help me out. Ideas?
Thanks,
GIScrew

 

[lbass]

I can't help if you only show the results--all we can work with is the pattern of the underlying data. If the data displays as you showed in your previous example, the running total suggestions should have worked. Please show the detail section AND the results in the group footer for one or two assessment IDs.

-LB

 

[GIScrew]

There are deposits with 0 balances and other non 0 deposits show up later. The loan count of 7 continues until all of the 133 deposits are accounted for. None of the loan balances in my database are over 110,000 for 102.03. Sample of the details section:

ID DpCount DpBal LnCount LnBal
102.03 1 0 1 3,687
102.03 2 0 2 2,361
102.03 3 0 3 45,496
102.03 3 0 4 144,441
102.03 3 0 5 151,074
102.03 3 0 6 259,284
102.03 3 0 7 320,049
102.03 4 0 7 2,361
102.03 4 0 7 6,048
102.03 4 0 7 51,544
102.03 4 0 7 150,489
102.03 4 0 7 157,122
102.03 4 0 7 265,322
102.03 4 0 7 2,361
//None of the loan balances in my database are over 880,000 for tract 108.
108 1 0 1 11,743
108 1 0 2 197,743
108 1 0 3 992,886
108 1 0 4 946,376
108 1 0 5 951,537
108 1 0 6 955,687
108 1 0 7 959,420
108 1 0 8 999,370
108 2 0 9 176,636
108 2 0 10 120,740
108 2 0 11 157,182
108 2 0 12 160,960
108 2 0 13 171,799
108 2 0 14 234,190
108 2 0 15 1,048,856
108 2 0 16 6,078
108 3 0 17 16,059
108 3 0 17 10,839
108 3 0 18 12,060
108 3 0 19 109,722
108 4 4,448 19 11,743
108 4 4,448 19 15,251
108 4 4,448 19 26,360

//Running totals for 102.03 and 108:

Id
102.03 133 1,691,183 7 2,361
108 220 20,564,682 29 15,251
-The counts are correct for the loans/deposits for 102.03 and 108.
//Ideas?
GIScrew

 

[lbass]

I honestly can't tell what you are showing me here--it looks like some combination of fields and running totals. What I need to see are the fields that contribute to the running totals, including the two objIDs, and you could show the running totals, too.

If you want to, provide your e-mail and I will contact you and you can send me a copy of the report with saved data to look at.

-LB

 

[GIScrew]

Ok, here is my email:
one@geo.net
Make the subject; Attention Grant:Running totals
Thanks,
GIScrew

 

[lbass]

Tried e-mailing you and it comes back as undeliverable--recipient name is not recognized.

-LB

 

[GIScrew]

I apologize;
geo@one.net
I've been out of it the last couple of days.
GIScrew

 

[GIScrew]

LB,
I decided to put that report aside on friday and not touch it until today. I ran through all your suggestions and discovered my problem. I unsorted the records last week before I applied your suggestions for the running totals, instead of leaving the records sorted. Today, I sorted the records and applied the settings for the running totals. Everything works great. I appreciate all of your help. One quick question. Example, say I want to run another report based on the same running totals and formulas in this specific report but I want to use another bank's deposit and loan data. What is the most feasible process of reporting on different tables?
Thanks,
GIScrew

 

[lbass]

I don't think I'm the best one to answer that question. Perhaps someone else can respond. You might be able to use the "set alias" function to make the table names the same, but the fields from the new tables would have to be the same as in the old ones. I've never tried this myself.

-LB