Hi Dan, > Maybe I do not understand SQL well enough, but I have a problem. > I have a table for fuel that I buy as well as the odometer reading > when it is bought. I would like to calculate the fuel economy > (quantity of fuel divided by the distance traveled). How do I > subtract the odometer readings from adjacent rows to get the distance > traveled? I have a few other databases which require subtracting one > row of a field from other row.
speaking strictly, relational database tables don't have a concept like "subsequent rows". That is, even if you insert rows in a specific order, you're not guaranteed they're returned in the same order - the database engine is free to change it. (In fact, some known engines do.) Okay, that was the nitpicking part, since you of course have an additional field (the date when you bought the fuel) which defines an "order" in the data records. However, standard SQL does not provide a possibility to refer to previous rows - that is, you cannot build an SQL query which subtracts the current row value from the previous row value (or the other way round). Such tasks are best solved in the reporting domain. Now, well, the reporting facilities which come with OOo 2.2 are not able to do this. Sad enough. So, if you're bound to 2.2, the only suggestion I (don't like to, but) can give: create a data range in a spreadsheet, which is filled from your table, and do the calculations in the spreadsheet. Besides this, I want to point you to the new report designer which will be available in 2.3. Therein, you will be able to solve this problem. If you're a bleeding-edge type, I suggest you wait for the announcement [1] of a public alpha (this week, hopefully), and try it out - every feedback in this early development phase is be appreciated, and will help us to improve the designer before the first public release. Ciao Frank [1] watch http://blogs.sun.com/GullFOSS for this, or [EMAIL PROTECTED] -- - Frank Schönheit, Software Engineer [EMAIL PROTECTED] - - Sun Microsystems http://www.sun.com/staroffice - - OpenOffice.org Base http://dba.openoffice.org - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
