counting real records
Richard Kreiss
rkreiss at gccconsulting.net
Mon Aug 12 11:06:55 PDT 2013
> -----Original Message-----
> From: filepro-list-bounces+rkreiss=verizon.net at lists.celestial.com
> [mailto:filepro-list-bounces+rkreiss=verizon.net at lists.celestial.com] On
> Behalf Of scooter6 at gmail.com
> Sent: Monday, August 12, 2013 1:21 PM
> To: Kenneth Brody
> Cc: filepro-list at lists.celestial.com
> Subject: Re: counting real records
>
> Actually, in my example there are 4 'physical' records (1234-0001 and 1234-
> 0002-1, 1234-0002-2, 1234-0002-3) But for purposes of what I need to
> calculate it should only be 2 records.....so yes, regardless of how many -1 -2 -
> 3 ,etc that are on the end of the full acct number, it should only count the
> 'record' as 1 entity So, let's suppose I have the following accounts for client
> number 1234:
>
> 1234-0001 Bal Due 150.00
> 1234-0002-1 Bal Due 200.00
> 1234-0002-2 Bal Due 200.00
> 1234-0002-3 Bal Due .00
> 1234-0003 Bal Due 800.00
> 1234-0004 Bal Due 500.00
> 1234-0005-1 Bal Due 300.00
> 1234-0005-2 Bal Due .00
> 1234-0006 Bal Due 50.00
> 1234-0007 Bal Due 150.00
> 1234-0008-1 Bal Due .00
> 1234-0008-2 Bal Due .00
> 1234-0008-3 Bal Due 100.00
> 1234-0008-4 Bal Due .00
>
> The report I'm trying to create should show there are: 8 records (0001
> thru 0008) even though several have -1, -2 etc and it should only calcluate the
> balance once for each of the 8 records with today bal due of 2250.00
> (keeping in mind, it needs to look at all -1, -2, etc in case only one of them has
> a bal due. So, in case of 1234-0008-1 thru -4, typically the original total bal
> due would probably have been 400.00, and -1, -2 and -4 paid their 1/4 of orig
> bal due, so -3 still owes
> 100.00
> So what I want is a report of total 'active' entities (not physical
> records) that have a balance and the total of the balances. So it should count
> those records without any 'associate' people as 1 record (i.e.
> 1234-0001, 1234-0003, 1234-0006 and 1234-0007 - obviously those are easy
> because it's just one person to that account number -- the issue is not
> counting 1234-0008-1 thru 1234-0008-4 as 4 records - just should be one
> record...and only grab the balance due once Hope that helps thanks
>
> Scott
>
>
> On Mon, Aug 12, 2013 at 1:02 PM, Kenneth Brody <kenbrody at spamcop.net>
> wrote:
>
> > On 8/12/2013 11:21 AM, scooter6 at gmail.com wrote:
> >
> >> Should seem simple but let me explain...
> >>
> > [...]
> >
> > 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.
> >>
> > [...]
> >
> > Why should those 3 records count as only 2? If you were shown those
> > records and told to manually count them, by what process would you
> > arrive at "2"?
> >
> > Is it purely the fact that client number "1234" has records with "0001"
> > and "0002", regardless of how many "0001" and "0002" records exist?
> > Or is there some other calculation involved?
> >
> > --
> > Kenneth Brody
The only thing I would suggest is in the report format sort, place field 1 and set it as a break field and your field 2 as the second sort. I will assume (bad word) that your selection set only selects records with a balance greater than zero.
This should print all of the records by client # and record # with a positive balance. You could then, at the break, have the total due for that client # and add *@rs to the break. This will give you the record count for those records in that break.
1. what file are you running this report from?
2. If this is a separate file, is there an index on the client #. If so, are you running this report with -In option set so the records come up in client # sequence?
You can write a -v select process to select the records and then print your report. Using sort/select processing allows you create options for running a report and even by using the sort option, change how the data is presented. In this case you could sort by client number and sort on balance due descending or ascending order. You could do a lookup to the client file and sort by zip code, city, or state. Or you could select only those clients in a given location.
Richard Kreiss
GCC Consulting
Office: 410-653-2813
More information about the Filepro-list
mailing list