|
I knew I could do a separate query. I was just asking to make sure I wasn't missing something and could get it all into one. -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi Sent: Monday, March 05, 2007 12:10 PM To: 'Midrange Systems Technical Discussion' Subject: RE: STRQRY and counts. 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.