Hi, in my working days I used to spend plenty of work hours with spreadsheets. I’m helping a friend in my retirement to create a database. I’ve not done much with dB’s. Here’s my problem. I have a table, ID (key), Date, Type, Description, Cost(£) and Rate(%). Each record is either Type “A” or type “B” and my report needs to list all types “A” in the first table along with totals at the bottom and all type “B” in another table, again with totals, all between certain dates that I’m assuming is part of my enquiry. I’ve also read that I can create mathematical results as each record is used, for example a third column that is the result of cost(£) * Rate(%), that is what I’m working on at the moment. Once all records are populated then I need a small summary at the bottom of the report that subtracts one total from the other. So, basically can you create a report that populates all type A’s in one list then all type B’s in another, do some simple maths etc. I’m assuming, it’s early days yet, that the report is used for the inquiry either on screen or print out…
Hello,
The bottom line answer to your question is yes, you can create a report with calculated information and totals. This can also be done with a form. The problem is getting from point A (starting - where you are at) to the actual end product. There is a lot of information and planning you need to address. Much of the basic information is in the LO documentation found here → LibreOffice Base Handbook. Further down on that wiki is a document - Tutorial: Creating a Relational Database using Base.
Usually when you have different record types there is a common factor relating them. For example, in construction you have a table for customers, then probably one for different jobs relating to a customer and within the job different items such as parts and labor which are also in different tables relating back to a particular job. Your table appears to mix different records without any relation to each other.
Once you have the necessary tables established you can set up input forms. With databases you typically do not store information which can be easily reproduced with data already stored. This would be something as parts (3 identical faucets) each with at the same cost. This total can be calculated using SQL (database communication language) when needed. There are databases which can do this as a record is entered (Firebird is one) but that requires another discussion.
After all your data is available, the data can be accumulated with SQL to either be displayed on a form or a report.
An example of this is available in my answer in this post → Libre Data - multi-level report. There you will find a sample Base file you can download and examine. On another post, I explain how you can do different calculations within Report Builder → How to reuse an accumulated value in a Report?.
Now while this is a lot of information to take in all at once, the best approach is to start with a correct set of tables and designing your database by not duplicating information. Then progress from there.
Please understand, this site is meant to answer specific questions and get an answer for that question such as display (or print) a calculated amount. Your question does seem to cover from inception to final product. Please insure future questions (always welcomed) and specific in nature. Future questions should typically indicate your OS, the LO version you are using (specific, not “Current”) and in the case of Base, the database you are using. Currently Base can default to either HSQLDB embedded or Firebird embedded database.