RE: STRQRY and counts.

["John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx>]

You can try a separate query that summarizes on BOB and produce a count on
Bob's to a file, then include that summarized file in your final query
pulling the outputted count 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Wallroff
Sent: Monday, March 05, 2007 11:44 AM
To: Midrange Systems Technical Discussion
Subject: STRQRY and counts.

I have a question on how to do what I think should be simple count in
the Query Utility.  I would like to be able to count how many times the
report breaks.

An example:  I create a query with 2 files.  The first file, the
Primary, contains a person's basic info such as gender, birthdate, etc.
The second file, joined by "Matched records with the primary file" by
ID#, contains all the addresses for a person.  Since a person can have
more then one address, perhaps a home address and a business address,
they can end up having multiple lines in the query output.  Let's say I
want a count of how many "Males" have an address in "Idaho".  I want the
output to contain every address in Idaho even if they have more then one
there.  I sort by ID# and have a break set up on that field as well.

Is there a way I can count how many people are included in the query?  I
want to count each person only once even though they may have multiple
lines because they have more then one address Idaho.  In other words I
want to count how many times there is a break.

The output should look something like this.

ID#     Name            Address
1       Bob             123 Main Street.  Boise, ID  12345
1       Bob             345 1st Street. Boise, ID 12345
2       Joe             678 2nd Street. Big Mountain, ID 23456
3       Sam             987 Madison Ave.        Little Steam, ID 34567
3       Sam             765 Adams Blvd. Deep Creek, ID 56789

Final Totals
Count 3

Right now the only "Count" I know how to do will give a total of 5.

I've never been able to figure out how to do this all in one single
query.  In the past I've had to run 3 passes.  The first one to print
out all the detail info I need.  I then run that one again and suppress
summaries to an output file, then I run another query over the output
file of the second to get a count of the number of lines.

Thanks.

John.