Showing summaries of multiple sets of records?

[JariV]

Hi,

I have a database holding information imported from a vehicle data collection system.

Each record consists of information collected during one workshift. The data includes driver name, date, total distance,information on driving, loading etc. times, speeds (max, average, at different RPMs), fuel consumpition (total, MPG, liters/100km) etc., as well as summary and average fields for most of the data.

Now I'm trying to figure out how to show data for comparing different drivers during user defined period (for example, during last month) on several factors, such as:

- driver's average fuel consumption during the time period (across the varying number of records within that time period)

- driver's average speed at various RPMs

Ideally, the information would be a single table, each row showing a driver's averages and other statistics during the time period. In addition, the averages of all drivers' stats should be visible on the same table.

So far I have managed to show just one driver's stats at a time using finds and self-join-relationships based on driver information, but showing the same information simultaneously for several drivers appears to be quite a challenge for a newbie.

Any ideas how to approach this would be greatly appreciated.

Jari V

[dbruggmann]

Understand I correctly, that you have all the data you describe in one file/table in your database?

This makes it quite difficult to achieve, what you want. It would be a lot easier, if you would split the database into two files/tables: One for the Drivers (Drivers.fp5; name plus auto-entered a Serialnumber as key) and one for the Drives (Workshifts.fp5; with all the other information).

For every workshift you would then create a new record in Workshifts.fp5, insert the serial number of the driver and enter all informations. the whole creation (and if necessary also deletion) of these records would ideally be done by script.

In Drivers.fp5 you could then have a list layout which shows all drivers and their various average data. The latter values would be calculated through a relationship between the serialnumber field in drivers.fp5 and the serialnumber field in Workschifts.fp5 (using the aggregate functions).

[JariV]

Thanks Detlev,

To be quite exact, most of the actual workshift data is imported into/stored in the main database (be it Workshifts.fp5). However, driver name is fetched from a related db called Drivers.fp5 using match field DriverID (which is included in the data imported into Workshifts.fp5 from the truck recording system.

If I used the Drivers.fp5 as the place to hold/show averaged data for the drivers, would it be possible to show averages for several drivers (averages of several records of each driver) at the same time by using scripting and aggregate functions?

Jari V

[dbruggmann]

Hello Jari

I reread your first post and have to admit, that i missed your point about entering a time period for the averages. So the answer is Yes and But:

Yes, you can define calculationfields like "Average(Drivers::WorkshiftData1)

Drive Summaries.zip

[JariV]

Thanks a lot Detlev,

The calculation field example you showed ("Average(Drivers::WorkshiftData1)