Sorting fields

[skynight]

I have a need to sort records for a given period (one month) using three or more variables, and cannot figure out how to do it. There are 10 vehicles, each with an ID number, plus another few privately owned vehicles, used for deliveries that are identified with a route number. I need to make up a monthly report showing the number of miles each vehicle has driven on the different routes, plus a total of the miles driven on the routes for all vehicles, with a separate total of miles driven on the different routes for the private vehicles as a group (don't need to break down the miles for each private vehicle, just the total for them all on each route).

I thought I could make up value lists for, say, each vehicle and each route, and a field for selecting corporate and private vehicles, then sort, but it is not that easy. If there are only three vehicles used in a month, for example, I don't want the list to show the unused vehicles since it would then be too big and hard to extract the information needed. Is there a way to make the field active and siplay information only when there is something to show? At present the data is being captured manually, and I know if I can do it automatically it will be acceptable but there is a lot of opposition to the new system so far. I have people making the selections and the monthly report from various locations and most have little knowledge of data bases, so it has to be easy, perhaps a button to select the information and another to print it.

Any help would be appreciated, and maybe I could learn how to do it myself and not show my ignorance.

[jamesducker]

Is there a table/file containing one record for each journey travelled?

Should contain fields:

- which vehicle

- which route

- how many miles

The journey is the smallest unit of data so it is that which will need to be summarised.

James

[skynight]

Yes, each record will have only one vehicle, and there will be only one route per record.

The ID of the vehicle can be manually entered, or selected from a menu. The route is also selected from the menu and the miles driven are manually entered.

I want the final result to show only those vehicles that were used in the month, with the associated mileage for that vehicle grouped by routes. eg Vehicle AA drove 14 miles on route 4, 73 miles on route 7 and 102 miles on route 8, a total of 189 miles, while Vehicle AC drove 66 miles on route 3, 13 miles on route 6 and 106 miles on route 8, a total of 185 miles for the period. If vehicle BB was not used that month, it should not show in the total summary. If private vehicles were used, they would be lumped together and only the miles driven on each route would be shown. And I want the summary to be automatically generated and displayed.

Should be easy, right?

Edited March 2, 2007 by Guest

[Ender]

Try a columnar list based in your Route table. Add a Sub-Summary by Vehicle Type part and below that, a Sub-Summary by VehicleID part. Insert a Total of Miles (summary) field on both parts along with the Vehicle Type and VehicleID to their respective parts.

When the records are sorted by Vehicle Type and VehicleID and viewed in Preview Mode or printed, the sub-summary parts will show along with their sub totals.

[skynight]

Thanks for the suggestion, but I cannot see my clients going for it. Is there a way to write a script that would sort the records first by vehicle ID and then by route? I read in the promo for FM8 that sorting is what data bases do best. I am competing with an Excel program, that completes this table, but it requires manual entry of the vehicle ID and each route total, then it calculates a summary. I want to go one more, gee-whiz level and have the program do all that automatically at the press of a button. I am sure there is a way; I just don't know how to do it but I'll bet many of you do know and I would be grateful for any help.

Again, I want to sort and display totals of miles driven on a selection of routes (A,B,C, etc) for several different vehicles (1,2,3, etc), each selectable. The final display should show all selected vehicles, with their individual totals of miles driven on each route and grand total of miles driven for each vehicle over the selected period. I figure a table with a drop down for selecting a vehicle ID, and then a button to sort the records by route and miles driven (15 miles on route A, 33 miles on route B, etc plus total miles driven by that vehicle for the period). Alternately, a table showing vehicles in the left column and routes by column across the rows, the last column showing the total. Entry in each field would be miles driven. Each base has only four of five vehicles and they all drive the same routes so I could tailor the program for each base. As the records are updated in the main layout, that info could be summarised in the report layout?

Desperate I am..