Updating records from a query again, again

Discuss the database features
Post Reply
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Updating records from a query again, again

Post by open_mike »

Good afternoon all,

I have switched from MS Access to Open Office Base because, literally I am just tired of Microsoft - And I am an IT engineer.

I have some Access DBs to recreate but need to do calculations and have those results stored in a field in the same table. To this end I created a test DB to learn.

I have learned from this site: viewtopic.php?t=64657 how to create a query that does the calculations so "step in the right direction".

Where I'm at now: It looks like (details to follow) that in making this work I will have 2 tables both the same size (and the record count can get pretty large); 1 table which a form accesses in order to enter data and a 2nd table which a query fills out with all the data from the first table plus the results. This seems redundant and I hope someone can help me actually put the results of calculations into the 1st table.

My Details:
I created Table1
PK = "ID" integer which is autovalue
value1, integer, user will enter a number in this field
value2, integer, user will enter a 2nd number in this field
answer, integer, results should be put in this field
Image

Query:
qryAdd, This query adds value1 and value2 from Table1: SELECT "ID", "value1", "value2", "value1" + "value2" FROM "Table1"
I did a Create as View to a table called Table2
Anyone know how I can stretch this to make the full table on top visible?
Image
Image


Table2 info:
ID - integer, no PK, no autovalue
value1 - integer
value2 - integer
COL_4 - BigInt
Image

Query2, This query gathers all the fields from Table1 and Table2 and places the calculated answer in the COL_4 of Table2
Image
Image

This is my form. Even though the calculations are correct you will see that for each record it shows the answer to calculations of the first record. Don't worry about this, I'm confident I can fix it.
Image
Image
Image

I hope I gave enough information and I hope I posted in the correct forum - It's difficult to choose sometimes

Anxiously awaiting responses, Thanks

Mike
FJCC
Moderator
Posts: 9217
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Updating records from a query again, again

Post by FJCC »

I may be missing something. I would store the ID, Value1 and Value2 in Table1. You can then write a query that returns those three columns and the sum of Value1 and Value2 or, if you want it to seem like there is a table storing that, make a View that does that.

Code: Select all

CREATE VIEW "Table2" AS SELECT "ID", "Value1", "Value2", "Value1" + "Value2" AS "Answer" FROM "Table1"
Storing the sum in a table opens the possibility of updating values in Value1 or Value2 and not updating the Answer column.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

Nice try and thanks for the response but that query gave me this error: The given command is not a select statement.

From what I've learned, Open Office base query's must begin with a SELECT statement.
Open Office 4.1.15
Windows 11
FJCC
Moderator
Posts: 9217
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Updating records from a query again, again

Post by FJCC »

You have to run the CREATE VIEW in the dialog brought up with the menu item Tools -> SQL. All queries that are not SELECT queries are run there.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

Interesting and thanks for the lesson.
I deleted Table2 and tried again this time from Menu-Tools-SQL

I tried CREATE VIEW "Table2" AS SELECT "ID", "value1", "value2", "value1" + "value2" AS "answer" FROM "Table1"
And got error: 1: Column not found: Value1 in statement [ SELECT "ID", "Value1", "Value2", "Value1" + "Value2" AS "Answer" FROM "Table1"]

I just had to change the cases of value1 & 2 and answer to lowercase to match what I had and it worked.

The results are the same. value1, value2 and answer all show up in Table2 (which is what I originally had)
Table1 only shows value1 and value2. The answer field is still blank
Open Office 4.1.15
Windows 11
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

To recap, my goal is to have the answer field in Table1 populated with the results of value1 + value2
Open Office 4.1.15
Windows 11
FJCC
Moderator
Posts: 9217
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Updating records from a query again, again

Post by FJCC »

Yes, my recommendation was to not have the answer column in Table1 because that can lead to update errors. Table2 can be used as if it were a table with the answer column but it is calculated on the fly and it will not have update errors.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

Thank you and I understand.
My thought is that as I enter records I can get up to 10,000 records in some cases which would mean that Table1 would have 10,000 records and Table2 would also have 10,0000 records thus doubling the size of the database.
If there is no other way then I can do this just thought it didn't make much sense.

Thanks for the help
Open Office 4.1.15
Windows 11
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating records from a query again, again

Post by Villeroy »

open_mike wrote: Tue Feb 06, 2024 7:49 pm To recap, my goal is to have the answer field in Table1 populated with the results of value1 + value2
Makes no sense. Why do you store a calculation result? When you change value1 or value2, the stored sum will not update.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

actually, during my tests I found that when I did change the values of value1 and value2, the result was updated in Table2
Open Office 4.1.15
Windows 11
FJCC
Moderator
Posts: 9217
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Updating records from a query again, again

Post by FJCC »

open_mike wrote: Tue Feb 06, 2024 8:48 pm Table2 would also have 10,0000 records thus doubling the size of the database
Table2 is not a stored table. It is a query stored within the database engine. That is, only the SELECT statement from the CREATE VIEW command is stored. Running that for 10000 records should happen very quickly.
open_mike wrote: Tue Feb 06, 2024 8:54 pm actually, during my tests I found that when I did change the values of value1 and value2, the result was updated in Table2

Yes, Table2 will update because it is a View and its SELECT statement is run every time it is called. If the sum of value1 and value2 were stored in a column in Table1, that column would not update upon changes to value1 or value2. You would have to separately update the answer column. If you ever forgot to do that, incorrect data would be stored.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Nick N,
Posts: 110
Joined: Wed Aug 09, 2023 2:10 pm

Re: Updating records from a query again, again

Post by Nick N, »

@ Open Mike

Good Evening Mike,

If you want to make it work, consider MainForm for Table1 and SubForm for the Retrieve View.

See attached odb file.

Regards

Nick
Attachments
Plus Operator Test.odb
(12.24 KiB) Downloaded 26 times
Libre Office 6.0.7 on Ubuntu 18.04
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

Interesting because there are no query's in your example DB.

It works exactly the same as mine (in mine I also use a sub-form to show results). But how did you do it without creating any query's?

Unless someone comes up with a unique work around I have already accepted the fact that the answers cannot be stored in Table1
Open Office 4.1.15
Windows 11
Nick N,
Posts: 110
Joined: Wed Aug 09, 2023 2:10 pm

Re: Updating records from a query again, again

Post by Nick N, »

Good Morning Mike,

Thank you for your reply.

Indeed I made only a view of the first table, because it behaves like a query itself. You may have noticed it.

Kind regards

Nick
Libre Office 6.0.7 on Ubuntu 18.04
open_mike
Posts: 8
Joined: Mon Feb 05, 2024 11:22 pm

Re: Updating records from a query again, again

Post by open_mike »

Hi Nick,

Yes I just noticed. Thank for explaining
Open Office 4.1.15
Windows 11
Post Reply