I have a database with several thousand records in it. While there are many fields in the database, I need to build a report on just two of the fields. One field is catagory. Each record is assigned to one of 5 categories. The second field is zip code. I need a report that shows how many of each category there are in each zip code. The database covers the entire US. I know that there must be a way to do this.

example of what I need:

Zipcode - Category 1 - Category 2 - Category 3 - Category 4 - Category 5 - Totals

10000 4 0 3 3 5 15

10001 2 2 0 2 0 6

20221 3 0 0 0 1 4

Any clues or ideas welcome.

Thanks,

Dave

Hi,

Quick answer for a start...

If you want these records to show horizontaly, I think you will need to have this report in a Zip db, and use it in a portal using a constant 1 to 1.

One line per zip and its 5 count(selfjoinOnCategoryandZipCode) next + a Total of counts.

It's always troublesome to do "horizontal" stuff in FileMaker ...

In your case I'd use 5 calc fields

IsCategory1 = If (Category="Category 1",1,0)

...

IsCategory5 = If (Category="Category 5",1,0)

and 5 summary fields, equalling the total of the 5 calc fields

Then it's just a matter of creating a subsummary by zip code

It doesn't seem to work. I created the count fields and the summary total fields. When I do a report with subsummaries, I get one line for each instance of a given zip code, and the totals are the same for all the zip codes. I've attached my test file.

zipcode.zip

Hello David

The summary fields must be placed in the subsummary part, while they're currently in the body

Drag them there and they'll work

duh..thanks...

I'm lost again....The original zip code calculation worked but now I'm trying to do the same thing for another field and I'm missing something. In this case I'm summarizing by county. Anyone have a clue?

You have to sort by your break field for a subsummary part to display.

Also, your "is_x" calc fields can be shortened. If(pt_factor="p", 1, 0) is the same as pt_factor = "p". It's a boolean, so it will give you a 1 if it's true and zero if it isn't.

OK...I understand and after the sort it works. However, one problem remains. I need to list all the counties on the report even if there is no data for that county.

If you have a record for the county, then it should appear when you sort. If not, then you can't display nonexistent information, though my higher-ups always seem to think it's possible.

I think that the solution might be to have a calculated field for each option for each county (leon_is_p, leon_is_r, etc.) It's long and hairy and difficult to maintain but I think it will work.

I'm still struggling with summary fields. I need a report that only has the summary part and not the individual records. What I find is that I can create a report with the body data, and summary part, and then remove the body data and it works great until I change the selected records. Then it doesn't display properly until I re-add the body part, display the report, go back to layout mode, the body part and re-preview it.

If you don't sort your new found set by the break field, no summary part will appear.

so if I sort by the break field, I don't have to have the individual records on the report?

Correct.