Nedd help generating a report

[Jesse Wright]

Help building a report or at least the reference table to generate some statistical data

To give you a quick overview, we are an archaeology firm so we survey coal permits, highway and other DOT projects, cell tower sites, water and waste pipelines and other developments. We do the cultural surveys and then all the data is published for the client.

So the fields I am focusing on for this table are (JobType), (Client), I have a calculated field (Year) that is giving me the 4 digit year based on the field (DateMailed).

So what I want to do is either have a sorted table that breaks the clients into their job types and then in Year fields I want a running total of reports for a given year. The data is in different tables already so I don't feel or at leat think there is a need to manually make a separate reference table.

So to elaborate:

(JobType)

(Client) Project Totals: (2004) (2005) (2006) (2007) (2008)

The finished product will be a form with a drop down for the JobType ValueList that would return results for a requested JobType.

So show me all of our Coal Company Clients and a running total for each year so I can determine if and/or identify clients to whom we may be losing business.

I hope this wasn't too convoluted, and I thank you in advance for your time, help, and expertize.

[Søren Dyhr]

Are your skills beyond this?

--sd

[Jesse Wright]

I guess not! I have been using FM for almost 2 years now getting 3 decades of crappy excel tables into something that resembles a database and up until this year I have never had the opportunity to start getting some statistical data out of it. I dabbled with summary fields and sub summary in a layout last week but didn't really know what I was doing. Thanks for the link.

The mailman just this minute delivered my FM 9 The Missing Manual and the PM 9 Developer Reference so I can some other good reference material now, thanks!

[Jesse Wright]

OK, I have another question.

I have the sub summary report and summary fields working, now how can I clean up the results. By that I mean, it lists the client each time there is a record for a project, so how could I omit the duplicates and still have the summary field return a "count of" to total the project per year

Doesn't it need to have it displayed in the report for the summary fields to return the "count of"?

[Søren Dyhr]

No it doesn't you can inverse torso it if you wish (remove the body part)!

--sd

[Jesse Wright]

ok one last question cause I can't think anymore. I have a calculated field that is taking a date field and giving me the 4 digit year. i.e. 2/5/2008 returns 2008, so this field is now just years. I need to return a total count of records by the year, per client for the subsummary report

What I would like to see:

Job Type: Transportation

Client: KYTC

2004 - 4

2005 - 7

2006 - 12

2007 - 18

2008 - 2

I should know this, my eyes are crossed and I'm going to lunch. If anybody has a clue as to what I'm doing help a brother out and get me back on track

[Søren Dyhr]

Year(theDate) is the calc' and yes you can sort and group by it, further more can you usher it to it's own column if you wish by a calc':

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

--sd

[Jesse Wright]

Søren you are a fountain of knowledge. Thanks I'll go read the blog

[Jesse Wright]

ok, well that example is dependent on fields collecting value specifics per record. My data iin its current condition is not as nice as i didn't have the luxury of manually inputting the data into a fresh database.

At the beginning I had to build "like" tables so I could move the data from their old Excel tables. So my problem is that I have one field that has a year for each record, rather than a field for each year and a corresponding value indicating the year.

So I'm thinking I need calculation fields to count the records by client for a given year or a summary field counting a running total restarted for each sorted group when sorted by year; though I'm not sure I know how to do it.

[Søren Dyhr]

A cause of less desired results is if one of the sortfields ins't the right field type, such as textfield being stored in a number field.

Anyway this one is made by the wizard, only thing I've done is to remove the body part, and then changed the generated script to give a figure of how many pages it might be producing, to prevent a ? over at the rolodex.

--sd

Torso.zip