Base Form data uneditable

I am using this to get VAT and Totalcost results from a query:

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”

It does bring up the desired results ok , but I found that the data is not editable, neither in the query nor the Form. I do have a PK set in the table where the data is editable.

i have made up another table with just 10 records, and all the fields set accordingly, be it number or decimal. I created another query based on the said table, then used the 2 part statement to create the desired results. After doing so the fields were still not editable .

What data are you trying to edit that you need a query?

Also I should have said to make it clear is that I cannot enter a new record, and when on the form, I cannot select a new record to add new data I may or may not want to edit any existing data, as I don’t know what will change yet.

You don’t need a query to edit tables or add new records on a form.

I’m not sure I am following you correctly, as I have always used queries in access to give or show the correct results on the form. If I don’t need a query, how will the correct results be shown on the said form.

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.

	"A"."Cost" + "A"."Delcost" + "A"."VAT2" AS "TotalCost1" 

	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.