Updating records from a query again, again

[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


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?




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


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



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.




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]

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.

[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.

[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.

[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_mike]

To recap, my goal is to have the answer field in Table1 populated with the results of value1 + value2

[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.

[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

[Villeroy]

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.

[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

[FJCC]

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.

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.

[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

[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

[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

[open_mike]

Hi Nick,

Yes I just noticed. Thank for explaining