Problem Creating a Report

[Papabjoe]

I need to generate a report. I am posting in this forum because I'm not quite sure where it would be more appropriate.

In my database I have a core "clients" table. My other related tables look like this;

"CLIENTS" > "FLEETS" > "UNITS" > "TRIPS"

Each "unit" makes several trips during a reporting period. The reporting period data is stored in a table called "CLIENT QUARTERS".

The "TRIP" table stores many records that contain the unit number, quarter, state that the trip took place, and how many miles were traveled in that state.

I am trying to build a report that will show for a particular "CLIENT" "QUARTER" and "FLEET" a list of "UNITS" and a summary of total miles traveled in each state for each "UNIT".

example:

ABC TRUCKING COMAPANY - QUARTER 1 2004 - FLEET 001

UNIT 01

AZ 500 MILES

CA 600 MILES

NM 550 MILES

UNIT 02

AZ 200 MILES

NM 300 MILES

TX 800 MILES

I've been working on this to the point of insanity. Rather than explain all of the steps I've gone through, I am hoping that somebody with a fresh approach will be able to give a better solution.

[RalphL]

As a rule of thumb print reports in the table that has the most data. This looks like it would be TRIPS. The report would be a list layout with many sub-summaries. After it is set up and working delete the body part of the layout.

[Papabjoe]

O.K.

That helps, but not all the way. I want to use the "go to related record" script step. What steps must I take in order for this to work?

I have created a table occurance of "trips" that is related to "quarters". When I select a fleet and go to related records I get a list of all units, all states, and a summary field that shows a grand total of all miles in the table.

[RalphL]

When you go to the related record are you using the show. Does this give you records in TRIPS that are just for the selected conditions?

Is the summaty field in a sub-summary part?

[Papabjoe]

O.K.,

I think I resolved my display problems. But now I am experiencing something new. The report function's properly but it only displays records for the first "quarter". In other words, I select "Q1 / 2004" and then run the script. It shows all of the correct data. But when I select "Q2 / 2004" and run the script, it only shows the data for "Q1 / 2004". My instincts tell me that the problem lies somewhere in my relationships.

Any advice?

[Papabjoe]

Never-mind. Problem solved. I needed to add a "set field" step to my script in order to get all of my relationship's key fields to work correctly. My relationship between quarters and clients was based on a match between two fields - client id and quarter id. The quarter id field in the client's table was set to quarter 1 of 04 by another script. By adding the step to this report script it now works just fine.

Thanx for you help.