Re: counting zip codes
montana wildhack
<FMP2...@earthlink.net> said:
> GetAsNumber ( Left ( GetAsText ( Zip ); 3))
I get the same results as before.
My calculation for the first three numbers was
Left ( ZIP; 3 )
I'm still unable to figure out how to add all the iterations of the
first three numbers.
zip examples 12345, 12346, 12347
summary gives me
Zip total
123 10
123 10
123 10
montana wildhack
> The post office wants our small non-profit to give them totals of the
> number of pieces of mail that go to each zip code. We have zip codes
> (for instance) that are 12345, 12346 and 12347. The post office wants
> the total for all the 123 codes.
>
> I know how to get the database to give me a total of the each of the
> zip codes and I know how to have the database just print the first
> three numbers, but my ugly solution gives me a list that looks like the
> following:
>
> 123 20
> 123 15
> 123 2
>
> Now, I can sit with my little gonkulator and add up the various 123
> numbers, but there has to be a way that I can ask the database to total
> all records from zip fields that start with 123. I'm just not
> experienced enough to understand how to do this and I don't find the
> help files illuminating.
>
> Any ideas for a liberal arts major will be greatly appreciated.
I forgot to add that I use Mac FM 7
news.east.earthlink.net
Make a calculation field that will determine the first 3 characters of
the Zip:
GetAsNumber ( Left ( GetAsText ( Zip ); 3))
Then you can summarize on this field in the same way that you are
summarizing on the Zip now.
-Mike P.
montana wildhack
news.east.earthlink.net
suspect you are still summarizing on the original Zip field?
-Mike P.
montana wildhack
<FMP2...@earthlink.net> said:
> You need to summarize (and sort) on the new calculation field. I
> suspect you are still summarizing on the original Zip field?
>
> -Mike P.
I was summarizing on the new field, but this does not mean that I
understand how to properly set up the summary!
I have a feeling I don't "get" that.
Howard Schlossberg
The sub-summary section on your layout should be set to summarize when
sorted on this new calc field. In this section on the layout, you
should add the calc field and your summary field (the count).
In browse mode, sort your records by the calc field and that should do it.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
montana wildhack
GetAsNumber ( Left ( GetAsText ( Zip ); 3))
with some success, but instead of giving me one page with a list of my
zip codes, I am getting a number of pages. The first page has 5 zip
codes and their counts. I have a blank page followed by a page with one
zip code and its count.
But I'm closer!
Howard Schlossberg
calc field. It sounds to me like you still are sorting by the full zip
code (or other field) as well, and you probably have a sub-summary part
on your layout that is set to skip pages after each change occurence.
--
montana wildhack
For whatever reason, I finally got this to work and I'm so pleased.
The volunteers at the not-for-profit will be very happy. I will be
spending a little less time doing free work for the mailings, which
makes me very happy.
Thank you
On 2005-02-20 13:54:17 -0500, Howard Schlossberg
Howard Schlossberg
NOT numbers. If you take a zip code for Boston, 02134, and you wrap it
with the GetAsNumber() function, the result will be displayed as 21.
Keep it as text to display it as 021.
--
Brian
Try this:
1. Create a new field to truncate the ZIP code to the first three
characters. We'll call the field trunc and it is a calc field with the
following formula: left(trunc,3).
2. Create a new field called constant. Set it to have an autoentered value
of 1
3. Create a self-joining relationship with the field constant (the new
relationship is to the same file/table you are currently in).
4. Create a new calculation field called count with the following formula:
count(relationshipname::trunc) where relationshipname is the relationship
you created in step 3.
5. Place the new count field on your layout. It should now count all
instances of the first three characters of any ZIP code you enter.
Good luck
Brian
"
montana wildhack" <mon...@wildhack.invalid> wrote in message
news:2005022010392116807%montana@wildhackinvalid...
montana wildhack
> 1. Create a new field to truncate the ZIP code to the first three
> characters. We'll call the field trunc and it is a calc field with the
> following formula: left(trunc,3).
> 2. Create a new field called constant. Set it to have an autoentered value
> of 1
> 3. Create a self-joining relationship with the field constant (the new
> relationship is to the same file/table you are currently in).
> 4. Create a new calculation field called count with the following formula:
> count(relationshipname::trunc) where relationshipname is the relationship
> you created in step 3.
> 5. Place the new count field on your layout. It should now count all
> instances of the first three characters of any ZIP code you enter.
Brian-
This was too much for my poor brain to figure out.
I followed the steps, but I could never get anything to appear in the
count field.
The other suggestions worked, although I'm guessing your fix, where I
evidently could keep everything as numerals, would have been better in
the sort.
Thanks for trying to help, though!
B Collins
montana wildhack <mon...@wildhack.invalid> wrote:
Field Zip3, a calc field, formula Left(Zip;3)
Summary field Zip3Count, formula Count of Zip3.
Layout with subsummary part when sorted by Zip3, and trailing grand
summary. Call the layout Count by Zip 3.
No Body part in the layout, only header, subsummary and trailing grand
summary parts.
In the subsummary part, put fields Zip3 and Zip3Count side by side.
In the trailing grand summary put field Zip3Count
Set up to display in 2 or 3 columns. I have found 3 columns works well
on letter paper in portrait mode.
In the Header put a title, such as name of your organization, Count by
3-digit Zip code, and date symbol.
Do a Find for the mailpieces you are sending (for example, maybe you
have 2,000 names total, but you only want to mail to 500, and you have
some criteria to find the 500 from among the 2,000). One Find criterion
you should always use is that the address actually has a Zip code; no
sense in printing a label for an address that does not have a Zip code,
or counting that in your count of Zip codes.
Sort by Zip3.
Go to your layout for printing mailing labels and print them. Now you
will get the labels printed in order of Zip3, so you can group the
mailpieces easily by Zip3 as you label them.
Go to the layout Count by Zip 3.
Voila! You have one occurrence of each 3-digit Zip, with the count of
the mailpieces for that 3-digit Zip, and a total count of all the
mailpieces. It should all fit on one sheet on a multi-column layout,
unless you are mailing to a very large number of different 3-digit Zip
codes. Print a couple of copies.
Fill out the bulk mail form using the information from your printed list
of Zip3 counts. Take it to the post office with the mailing. The post
office will happily take your printout of the count of mailpieces by
Zip3, along with your bulk mail form.
You can automate this by use of scripts, to make it even easier next
time.
Bill
--
To send e-mail, remove .invalid