Don’t know what correct results means in this context. Table maintenance (add, change, delete) is typically not done with queries. Since this is a major (and very lengthy) discussion, and all written down already in the documentation, once again, please refer to the LO docs - click here. Chapter 4 = Forms.
Correct results means getting the results from a calculated query.
What am I looking for specifically, as I know how to design forms and add text and list boxes etc.
All I want is for my existing databases work in base as they do in access.
I might just call it a day now and go back to access, and transfer data from 2 databases to calc where I can easily get what I want from them
I apologies for wasting your time.
Thanks anyways
It has nothing to do with your asking questions. Your last comment specifies calculated results. This has nothing to do with entering or changing data since any calculated information based upon other fields needs no entry/maintenance.
No I don’t think you are understanding me now. If you have ever used MS Access, that should help you to some degree. Calculated results are the result of calculating by whatever means 2 or more figures to give an answer. So if I want to know what the vat is on a £329 , in Access it is a simple multiplication: 329*.20 =65.8.
All the data is editable whithout the TotalCost1 SQL statement you gave me, but is not editable when I use the said SQL and neither can I add a new record to the form
Why have a database when you cannot add new records.
Base is not Access. There are things which can be done in each which cannot be done in the other. There are no calculated fields in Base. This can be done with macros but that is way different in Base than in Access. Are you looking to display or store the amounts?
For further clarification, your query required two calculations: VAT1 = Cost1 *.2 which was needed for your actual request - “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1”. Is this correct?
Yes this one is correct: “Cost1” * .2 AS “VAT1”, which I found only gave the VAT1 figure to only about a 3rd of the records
And yes, the second one/statement was what I was thinking would work, but it doesn’t, as I get a syntax error and from an SQL command window it is Unexpected token Cost1, requires FROM in statement [SELECT…all the field names.
Please see the sample in my post. It is a working example. As far as the ‘3rd of the records’, is there an existing value in “Cost1” for all the records?
With multiple answers it is important to know who you are responding to. To maintain clarity, please use the add a comment below the answer you are going to respond to.
Yes, I am well aware that base is very different to Access, and I have read on various message boards that base is not even a database and it is HSQLB, which is a database engine used in open source projects.
I wish to display and store values which a sum of 2 or more fields, VAT on 328 = ???, and also the TotalCost inclusive of Cost, Vat and any delivery charges. This is entirely for my own personal use.
Just for clarification, as I have not said what the fields are. The cost, delivery cost and vat are all currency types. So for me it is £ and pence that I need to calculate.
@Avvy65, you wrote, “All I want is for my existing databases [to] work in base as they do in access.” I am a long time Access lover, but hated Win10. It was a challenge to move to Base. There were two things required: 1) motivation & 2) accepting that I needed to spend the effort to learn how Base worked. If you have the motivation, I suggest you gradually transition, as it is a big step to do the port in a reasonable way. Move Access data to shared DB first (MySQL) so Access still works.
Then you can fiddle with Base and slowly get things to work. It’s a very different world from Access, and there are many things that are better about Base, but also things that Base can’t do and you will have to re-think how you do some things. LO is a community project, not a corporate project. It plays out very differently. It might not be right for you. But I have learned to love Base and am able to do most of the things I used to do in Access.
So, if you are looking at just displaying the two calculated fields, a sample is attached. The Query was modified to create two independent calculations within one statement. The fields are not connected to the table in any way and cannot be using this method. It would probably take macro coding to do that.
Sample: DisplayCalcFields.odb
If the query got more complex (as multiple SELECT statements) this is not possible.
Edit 5/10/17:
The following sample contains the form originally in the previous sample (display of calculated fields) plus an additional form which is based upon a macro to insert the calculated values into the proper fields for updating.
Sample: UpdateCalculatedFields.odb
Again, macros in LO are quite different than Access. For basic info on macros, see chapter 9 - Macros in the LO documentation. For a more complete guide on LO macros (quite large doc) there is Open Office Macros Explained. For OOME pdf - click here.
The macros in the sample need adjustment to your needs and table/fields names.
Queries are not always editable, and there are a whole bunch of things that can make a query non-editable. Rather than go into that list, which you can google and find for yourself, I suggest you start with a simple query that works, and slowly add to it rather than start with a complex one and then try to figure out why it doesn’t work.
Also I think your questions are too long. Please simplify your questions. Ok, let me tell you what I mean. When things aren’t working, YOU, start by making a copy of the database, then opening the database copy in destroy is ok mode. In this mode you can start to remove things that don’t directly relate to the problem you are having. In your SQL you can remove a whole bunch of fields, till you’re down to just an ID, and two fields to add together, so you can prove out the basic functionality that you need, i.e. you don’t need to list every field in your SQL, just key ones that relate to getting things to work.
This does two things, one it helps you see your own problem more clearly, and removes possibly complications which might be causing issues. Two, it allows you to upload your database here so others can look at it and give more helpful answers to you.
Here is what I’ve noticed. First "A"., (after "EbaySale",) doesn’t look right to me. It looks like an error.
SELECT
"ID",
"BuyDate",
"item",
"Quantity",
"OrderID",
"Cost",
"Vat",
"Delcost",
"TotalCost",
"SupplierPhone",
"SupplierEmail",
"Notes",
"SupplierAddress",
"SupplierName",
"EbaySale",
"A".,
"A"."Cost" + "A"."Delcost" + "A"."VAT2" AS "TotalCost1"
FROM (
SELECT "Table1Test"., "Cost" * .2 AS "VAT2" FROM "Table1Test"
) "A"
Next, I don’t think HSQLDB 1.8 supports sub-queries, at least not very well.
Also you haven’t said anything about what type these fields are. Are they numeric? Do they allow nulls? Nulls anywhere in sums or concatenations often results in a null result. To avoid this you must convert any nulls to zeros (for numeric values) before doing your addition. Or you must ensure that all fields are populated with data. In Access we used this great little nz() function to do this. In MySQL / MariaDB I use the isnull() function. In HSQLDB 1.8 you must use the IFNULL() or COALESCE() functions. So it’s important which database you’re working in. (Always please say which database you’re working in in your question.)
Now this works in HSQLDB 1.8 to add fields. It uses this SQL: SELECT "ID", "n1", "n2", "n1" + "n2" AS "total" FROM "Table1"
So you probably have an issue in your FROM clause. Without having your table to try this out on, I can’t offer SQL to you that will work. But I suggest you try to get rid of the subquery, or move to a database that fully supports them, like MariaDB, etc.
I wasn’t aware I was using a subquery, and if you could point out where I may find it , that would be great.
@Avvy65, It’s the
(
SELECT "Table1Test"., "Cost" * .2 AS "VAT2" FROM "Table1Test"
)
in your FROM clause. Read about them here.
If I understand your question correctly you have created a Form based on the Query you quoted. This Query was the subject of a previous question. The Query uses a calculated Field in another calculated Field and to do this the two step Query you now have is required. You find that you cannot add a new record through either the Query or Form.
I have done some tests. A Query with two calculated Fields both of which use Fields that exist in the Table and do not use one calculated Field in the other. The Query allows the creation of new records. A Form based on this Query gives error “Error writing to database. Input required in field (name of first calculated field). Please enter a value.” Entering any random numbers in both the calculated fields adds the record and the calculated Fields random numbers are replaced by the correct calculated values. This is due to the Fields Data property - Input required - being set to the default Yes. Setting both the calculated Fields to No results in new records being able to be created.
With one of the Fields calculated values using the other calculated value a new record cannot be created, as you found. The Query has the New Record icon greyed out. It appears that this cannot be done.
The way to achieve what you require is not to use one calculated Field in the other. You can create a simple Query with “Cost” * .2 AS “VAT2” and “Cost” + “Delcost” + “Cost” * .2 AS “TotalCost1”. The vat is calculated again in the TotalCost so you are using only fields that are in the Table. So your query would be -
SELECT “ID”, “BuyDate”, “item”, “Quantity”, “OrderID”, “Cost”, “Vat”, “Delcost”, “TotalCost”, “SupplierPhone”, “SupplierEmail”, “Notes”, “SupplierAddress”, “SupplierName”, “EbaySale”, “Cost” * .2 AS “VAT2”, “Cost” + “Delcost” + “Cost” * .2 AS “TotalCost1” FROM “Table1Test”
Also make sure you set the Input Required Data Property for the calculated Fields on the Form to No.
First, I thank you for your patience and understanding.
Setting the Field Data property to No did not make the fields editable , and I checked and checked again field data property was set to No but still not editable.
Thanks for the SQL statement above, which is quite tidy, but I found that for some unknown reason to me that only around a 3rd of the records were updated with a TotalCost1, that is from 351 records only 124 were given a TotalCost1 figure.