Zip or Postal Codes: Sorting and Counting

[RJN]

I have a 350-seat performance space. For each performance we ask our patrons to give their Zip/Postal Codes at the door when they enter. I need to keep a record of each performance and number of each Zip Code recorded. Most fall in the range of the 40-or so Zip Codes in our metropolitan area, but some happen to come, at random, from Zips we have never recorded before, perhaps from outside the state.

I need some advice on the fastest and/or most efficient way to enter all 350 Zip Codes in an individual record for each performance, and have them, if at all possible, self-tabulate as to the number of patrons from each Zip Code. Can anyone point me in the right direction as to method? If I were doing this in Excel I would use something like a Pivot Table - is there a FMP equivalent?

Ultimately we have to add up the totals of each Zip Code for the entire year, but I believe I have a focus on how to do that. Thanks.

Edited March 21, 2006 by Guest

[Lee Smith]

How about a value list of the zip codes, base on the Field entries.

Also, with version 8 came the ability to have Type-ahead for a Text Field (works similar to Excels way). You would need to format your field as text, and it will use already entered data in the field.

If this data is being obtained via web, or some other text (etc.) format, then there are ways to import it.

HTH

Lee

[aldipalo]

This may be overly simple, but....

Event table

eventID

Seat#

EventType :

EventDescription :

EventDate

ZipCode Table

EventID

Seat# (Perhaps drop down value list - auto complete) Usually theaters, etc number seat by A1,a2 b1,b2....z1,z2 so the list could be filtered as well)

ZipCode

City

State

ZipCode LookUp Table (Contains all zipcodes in USA)

ZipCode

City

State

Relationships:

Event Table::eventID <-> ZipCode Table::EventID

ZipCode Table::Zip Code <-> ZipCode LookUp::ZipCode

Layout:

Event

Portal>ZipCode Table

Seat# - ZipCode - City - State

You can now enter each zipcode based on event. Filter zipcodes by event or create reports based on all events by zip, etc.

******************************

Oops, didn't see your response Lee.

Edited March 21, 2006 by Guest

[LaRetta]

Another idea to get the counts ...

Zip Codes should be records. Then if you wish to count how many of each zip code, use a report. Create a summary field called sCountZipCode (number). Create a new layout; columnar list; Grouped; with Sub-totals. Fields will be zip code and sCountZipCode, Category will be zip code. It must be sorted by zip code. Delete the body after moving the zip code and sCountZipCode fields up into the leading part. Sort by zip code.

You will end up with a report of counts of each zip code.

UPDATE: When I say zip codes should be records - I mean each SEAT should be a record with a zip code field.

LaRetta

[RJN]

Many thanks for above -- will investigate each idea. There is no need to identify a seat number with a zip code: the greatest degree of specificity required is to gather zip codes relevant to a particular performance. Ultimately, simplicity and speed of operation for the office staff are most important, since time is tight and assessment of zip codes is important material for our funding stream as a non-profit arts organization.

Thanks again. Encouraging to know there is so much knowledgeable and enthusiastic help out there. Any other ideas, anyone?

Thanks again.

[comment]

As LaRetta said, if you hope to get any statistic information out of this, each zip code must be entered into its own record. The record should also note the performance ID. Entry can be done very rapidly directly into a portal.