Fuel consumption of cars and motorcycles

[Villeroy]

Are you riding bikes or driving cars from 20th century without much computing on board? If a vehicle has no precise sensors attached to a board computer, it is common practice to calculate the ratio between distance and quantity of fuel between two tank stops where the tank has been filled up completely. In the long run any impreciseness of the state "full" will compensate and you get realistic consumption rates.

This database example keeps track of gasoline expenses and consumption rates. The database document has a German input form "Tanken" which calculates the consuption as volume per 100 units of distance. Liters per 100 km is the commonly used term here in Continental Europe. The US version "Fueling" has English labels and calculates a range as the distance per volume (miles per gallon if you enter miles and gallons). You can safely delete the the form you don't need. I leave my test data in the database which include two vehichles "Kawasaki" and "Porsche" with fueling records for the German context. The figures do not make real sense in the US context since these data are based on metric units and euro prices.

After a refueling stop, open the form, click the green input section, enter the mileage, check if the tank was full or not and enter two figures price and volume or amount and volume or price and amount. The 3rd value will be calculated and must not be entered. Entering a date is optional. In case of a missing date the current date will be stored.
Alternatively, focus the white table grid, hit the >* button or shortcut Ctrl+PgDown which takes you to the new row below the last record and enter your data there.

Detailed description of the input form

Section 1: In the top left section you select a vehicle by clicking on it.

Section 2: The white tabular view in the top-right section represents the fuelings of the selected car, sorted by mileage with the latest fueling on top. Here you can edit and delete existing fueling records. Adding new fueling records is possible as well. The toolbar which is attached directly below the table view relates to that table view only.

Section 3: The bottom left section shows 2 columns with calculated distances, overall quantities, average prices and consuption rates. The left of the two columns shows the overall results since the very first fill up. The right column shows the current rates between the latest fill-up and the second latest fill-up. Both calculated columns are related to the selected row in the table view so you can navigate back in time.

Section 4: The bottom right section in green color takes new fuelings only. It comes with 2 buttons. The left one saves the new record and starts the next one, the second button cancels the current edit. Start with the focus in the date or mileage box and tab through the input boxes until you get the first button focussed and hit Enter. In order to cancel the entry, tab until the cancel button. This way you can enter many records fluently without using the mouse. New records that have been entered into this green section will be visible in the above table view after clicking the refresh button in the middle of the attached toolbar.

If you want to enter new records directly into the white table view of above section 2, click anywhere in the grid and hit Ctrl+PageDown or hit the ->* icon on the toolbar in order to move the curser into a new row. The Escape key will cancel any started input. Tabbing beyond the current row saves the record and starts a new one.

Some troubleshooting

I run the latest LibreOffice which asks me if I want to convert the embedded HSQL to embedded Firebird.
Although I did not try, I'm fairly sure that this would destroy the database. Click the [Later] button to keep what you have. If you want to get rid of the warning and get a safer database less susceptible to data corruption than any type of embedded database, convert the embedded HSQL database into a stand-alone database. Download, install and run my Python script from topic viewtopic.php?f=21&t=86071. It is a matter of seconds.

Something is broken. I can't get rid of error messages.
A new record or a modified one is stored when you click the save button or when you move to any other row.
Any invalid or incomplete record raises some error message leaving the modified record unsaved. In order to continue working with the form you have to fix your input or cancel the current input by hitting the green form's cancel button or by hitting the Esc key while editing the table grid. If the error message starts with "Attempt to insert null into a non-nullable columns..." then you have not entered the mileage. If it starts with "Check constraint violation CONSTR_QPA ..." then you have not entered two of three values quantity, price and amount.

How do I get rid of the example data for the two vehicles "Porsche" and "Kawasaki"?
The easy way: Open the table "Vehicles" in the database document's tables section and mark them as hidden by clicking the "H" field. Now my test vehicles are hidden but the data are still availlable. This is how you may want to treat your former vehicles in future. The database does not allow deletion of vehicles having any fueling entries. If you really want to delete all data related to some vehicle, open the form, select all fuelings by clicking on the the table view's grey top-left corner, right-click the record marker column left of the date column, choose "Delete..." and confirm that you want to delete all the records without any recovery option. After you deleted all fuelings of a vehicle, you can delete the vehicle by right-clicking the record marker left of the vehicle name.

How do I load my most frequently fueled vehicle on top of the form?
Go to the tables section and open the "Vehicles". The vehicles table has a column named "ORD" where you can enter integer numbers between -128 and +127. The smallest number appears on top. Don't do anything with the ID column.

How do I import my own data?
Open the vehicles table and enter the names/descriptions of your own vehicles. Note the automatically assigned ID number for each vehicle.
Create a temporary spreadsheet with 7 columns, preferably in this order
1) Date of fuelage (optional, can be empty
2) Price
3) Volume
4) Amount
5) Full tank (1 or 0)
6) Mileage
7) The Vehicle-ID from table "Vehicles"

Since the database accepts only 2 of 3 values in columns 2 to 4, you should calculate 2 of the columns, copy and paste-special values. Then delete all values from the 3rd column. For instance, you can calculate 2 complete columns with prices and volumes together with a blank amount column.
Copy the spreadsheet range, select the icon of table "Fuelings" and paste. A dialog pops up.
Table name: Fuelings
Action: Append data
Check option "Use first line as column labels" if your sheet data have column labels on top.
Click [Next>] and [Create] if the source table columns are in the correct order side by side to the respective destination columns. If not you can fix the column order.

How do I translate the input form without breaking things?
First of all, copy one of the existing forms by selecting the form's icon and paste. You will be prompted for a form name. Then right-click the new form icon and open it for editing.
Modify labels of table grids: right-click column header and choose "Column...". Enter a new label text into the property dialog.
Modify stand-alone labels: Ctrl+Click a label in order to select it without its associated input control. Then right-click->"Control..." and enter a new label text into the property dialog.
Switch between calculation of range and consumption: In the bottom-left section there is a violet box showing the overall consumption or range and a yellow box showing the current consumption or range. Ctrl-click one of the two boxes, then right-click and choose "Control...", select the "Data" tab of the properties dialog and choose a "Linked Field" property from the drop-down box. "Consumption" calculates consumption as quantity per 100 units of distance (Liter/100km). "Range" calculates the range as distance per quantity (Miles per Gallon). Repeat the change for the other box.

How about the single report in the report section of the database document?
I created a report based on query "qReport" which collects all the stored and calculated information for all fill-ups of all vehicles. The report has been created with LibreOffice. You need to install the "Report Builder" extension before you can open the report with OpenOffice. I tried to add some charts to this report but charting in reports is broken in both suites LibreOffice and OpenOffice. Reports with charts would definitively add some value to this solution.

[Villeroy]

Somewhat fixed the reporting issue with a workaround that applies to LibreOffice Calc: viewtopic.php?f=83&t=100415
A pivot table is an excellent reporting tool and LibreOffice can create dynamic charts from pivot tables.