counting real records

[scooter6 at gmail.com]

Should seem simple but let me explain...

We have a master database that has over 1 million records in it...
Here's how we assign 'account numbers' to them.

Every account has 3 account number segments...
Field 1 = Client Number
Field 2 = Record Number from that client
Field 3 = Those two fields concatenated together with a "-" in between

Some records will only have 1 person associated with it
i.e.  1234-0001  John Smith   Balance  150.00
So, field 1 = 1234
Field 2 = 0001
Field 3 = 1234-0001

Some records will have multiple people associated with it
i.e. 1234-0002-1  John Smith   Balance  200.00
      1234-0002-2  Mary Smith   Balance 200.00
      1234-0002-3  Billy Smith    Balance       .00  (paid their portion)

In each case, each one of those should ONLY count as '1' record

What I'm trying to do is get a count for all records, soley based on first
9 digits
So, if I run processing for client 1234, using the above example, it should
show me I have 2 records (1234-0001 and 1234-0002, regardless that
1234-0002 has -1 -2 and -3 associated with it.
Each of these will also have a balance associated with them.  I'm only
wanting to pickup the balance 'once' for each of these records, even if one
of the '-1, -2' etc has a zero balance.

So, the end result if I was to run a summary for client 1234, using the
above example, I want to get totals of:         Total Accounts: 2
Total Balance:  350.00  (150.00 for 1234-0001 and 200.00 for all the
1234-0002's)

I want a summary report that will give me these totals by all clients,
entering a client range.
So far, I have this:

Placesum (output processing)
This seems to drop the first record of each client?
aa is total original balance, ab is total paid, ac is current balance
Remeber field 2 is the -0001, -0002, etc which I set to qz
Field 1 is 1234 (clt number) which I set to qq
Line 7 below just grabs the client name etc out of the client file and
isn't doing any
calculation....I guess I have something wrong with the first 3 lines?
Any thoughts on this?  Hope I explained this well


    1  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -
-   -
gotit  ■ If: 2
=qz                                                             ■
       Then:
aa(8,.2)="";ab(8,.2)="";ac(8,.2)=""                               ■
  2  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■ If: 2 ne
qz                                                           ■
       Then:
aa=20;ab=21;ac=22;ad(7,.0)=ad+"1"                                 ■
  3  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■
If:                                                                   ■
       Then:
qq(4,#,g)=1;qz(4,#,g)=2                                           ■
  4  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■
If:                                                                   ■
       Then:
end                                                               ■
  5  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
@wbrk1 ■
If:                                                                   ■
       Then: lookup cln = client  k=1    i=a
-nx                             ■
  6  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■ If: not
cln                                                           ■
       Then: ha="Client Not
Found";                                            ■

  7  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■ If:
cln                                                               ■
       Then:
ha(30,*,g)=cln(2);hb=cln(4);hc=cln(5);hd=cln(6);he=cln(7);hi=cln(8)
  8  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
       ■
If:                                                                   ■
       Then:
end                                                               ■
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130812/7ccb9978/attachment.html