When I run the said query in tools/SQL Command window I get this:
1: Unexpected token: ELECT in statement [ELECT]
Also when I use it in the sql statement it gives a syntax error, which may correlate with the above.
You didn’t even copy it correctly - nowhere in my statement is there ELECT - it should be SELECT. Please look at what you are doing.
While you can run it there, you will see the results better if run in a query.
@Avvy65 Please use the new statement in my answer the old being incorrect for the reason given by @peterwt. “VAT1” is already in your table so instead the statement needs to use a different name - “VAT2” is what I chose.
Apologies about my earlier post, the S was missed from the word SELECT and it was late here.
The new 2 part statement works correctly now, except the query is not editable, so I can’t add new record, and there is a PK in the table, and it is editable
Based upon your last remark, the query is producing the requested calculated fields as your original question asked. This question has been answered and should be marked as such.
However, it sounds like it is not going to accomplish the end result you were looking for. There are so many different ways to accomplish tasks in Base, that unless the actual desired end result is specified, answers can only be given for what is asked. Please use a new question. It may not even require a query.
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 not editable.
I just wanted to rule out anything from the original table which may have caused the records not editable.
Queries are typically not used to generate editable data. They are to gather information for viewing in a way different than what was stored. This can be information from multiple related tables and include certain calculations and more. They also are used for reporting, summation and statistical purposes.
@Avvy65, queries can be used to edit data both in Access and Base. Access has many of the same things that cause un-editable queries as Base. Base however is lacking the ability to lookup related fields in queries. So in Base for related tables you have to build a form. I hope that someday we can upgrade Base to have some of the abilities that Access has that makes queries more usable. For now, if I have anything other than a simple table I build a form to edit the data.