[Solved] Updating records from a query again
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
[Solved] Updating records from a query again
Hello
First post, so please be gentle with me.
I have spent most of the evening reading previous posts and have learned that Base cannot update records while in a query using more than one table.
I am a coin dealer and am trying to migrate from a horrid bloated spreadsheet with about 50 worksheets into something a bit more sophisticated.
I have constructed my database on the principle that no information is duplicated, and that every piece of information is stored in its logical place, but this has meant that I have to use queries to see what I need to see. Now that's fine, if seeing is all I want to do...here's the query: As you can see, all the useful information about each coin is held in the Inventory table. The Coin table merely holds the InvID. Equally, all the useful information about each customer is held in the Customers table (which is actually a query, but that's another story!). The Coin table merely holds the CustID. The query does exactly what it is supposed to do, and displays just what I want to see.
But how would I change any of the values in it? I can't just edit the tables because the IDs are meaningless without the information that they link to. I'd never find the right record - the Coins table has nearly 10,000 records - hence the desire to get it all out of a spreadsheet!
It is early days, so it's not too late to do major design changes, but it will be in a week or so, so I have to get my head around this soon.
thanks in anticipation
Declan : )
First post, so please be gentle with me.
I have spent most of the evening reading previous posts and have learned that Base cannot update records while in a query using more than one table.
I am a coin dealer and am trying to migrate from a horrid bloated spreadsheet with about 50 worksheets into something a bit more sophisticated.
I have constructed my database on the principle that no information is duplicated, and that every piece of information is stored in its logical place, but this has meant that I have to use queries to see what I need to see. Now that's fine, if seeing is all I want to do...here's the query: As you can see, all the useful information about each coin is held in the Inventory table. The Coin table merely holds the InvID. Equally, all the useful information about each customer is held in the Customers table (which is actually a query, but that's another story!). The Coin table merely holds the CustID. The query does exactly what it is supposed to do, and displays just what I want to see.
But how would I change any of the values in it? I can't just edit the tables because the IDs are meaningless without the information that they link to. I'd never find the right record - the Coins table has nearly 10,000 records - hence the desire to get it all out of a spreadsheet!
It is early days, so it's not too late to do major design changes, but it will be in a week or so, so I have to get my head around this soon.
thanks in anticipation
Declan : )
Last edited by declanwmagee on Sun Sep 29, 2013 5:30 pm, edited 1 time in total.
LibreOffice 4.1.1.2 on Windows 8
Re: Updating records from a query again
Decian:
Welcome to the OpenOffice / LibreOffice Base Forum.
The quick answer to your question is . . . in order be able to UPDATE ( change ) any content of data . . . using a Query ( like you are using in your example ) . . . you MUST include in the Visible fields . . . each field that is a part of a Primary Key , for each table.
According to the graphic you provided ( and, that graphic is most helpful to be able to answer your question, so, thank-you for including it ) . . . the fields that comprise Primary Key(s) include:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Welcome to the OpenOffice / LibreOffice Base Forum.
The quick answer to your question is . . . in order be able to UPDATE ( change ) any content of data . . . using a Query ( like you are using in your example ) . . . you MUST include in the Visible fields . . . each field that is a part of a Primary Key , for each table.
According to the graphic you provided ( and, that graphic is most helpful to be able to answer your question, so, thank-you for including it ) . . . the fields that comprise Primary Key(s) include:
- "Inventory"."InvID"
- "Coins"."CoinID"
- INSERT ( add ) new records
- UPDATE ( change ) table content
- DELETE ( remove ) records
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Well, thank you very much SlideRule, for getting back to me!
While trying out your solution, I noticed that two of my tables PKs had disappeared, so I tried to put them back, and got a new errormsg, which told me that the SYS_IDX was broken. I have since found out that this is because, while trying to fix it myself last night, I changed all the table and field names to use ALL_CAPS, because I read that was good practice.
So I am starting again - luckily it is early enough in the project to be able to do that - I'll let you know how I get on!
thanks again for taking the time to help - you must get the same questions over and over....
Declan : )
While trying out your solution, I noticed that two of my tables PKs had disappeared, so I tried to put them back, and got a new errormsg, which told me that the SYS_IDX was broken. I have since found out that this is because, while trying to fix it myself last night, I changed all the table and field names to use ALL_CAPS, because I read that was good practice.
So I am starting again - luckily it is early enough in the project to be able to do that - I'll let you know how I get on!
thanks again for taking the time to help - you must get the same questions over and over....
Declan : )
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Oh dear. I can't even get it to create the tables this time around. I am only pasting in from Excel. All my column headings are ALL_CAPS so that should come over nicely. This stage worked quickly and well the first time around, now Base just goes straight into Not Responding mode and gets stuck there.
I hope I haven't broken Base now!
I hope I haven't broken Base now!
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Well, I don't know what I've done. Last night Base was creating tables from pasted data beautifully. Now it isn't even getting close. Just "Not Responding" and the eternal timer.
I think I'm going to have to go back to Spreadsheets, which is a real shame. I thought I was doing so well!
I think I'm going to have to go back to Spreadsheets, which is a real shame. I thought I was doing so well!
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Well, would you believe it. My table creation problem was blinkin' Excel not giving up its data properly. I tried moving it from XL to Calc first, and then Base accepted it quite happily. Why Excel was happy handing it over last night, I still don't know, but hey.
Sorry for the running commentary, but I didn't want anyone to expend any energy thinking about a problem I had already solved!
Now back to making the Update query work....
Sorry for the running commentary, but I didn't want anyone to expend any energy thinking about a problem I had already solved!
Now back to making the Update query work....
Last edited by declanwmagee on Sun Sep 29, 2013 3:21 pm, edited 1 time in total.
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
OK, back to Square One. New dB, new tables, new query:
PKs from Coins and Inventory included and visible.
Results are good, but still Read-only. I am just trying to edit the data in this:
not in a form or anything, just the data.
Any ideas?
PKs from Coins and Inventory included and visible.
Results are good, but still Read-only. I am just trying to edit the data in this:
not in a form or anything, just the data.
Any ideas?
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Latest:
If I exclude the Customers table from the Query, it works - I can edit the data in Grid view.
As I said in the OP, the Customers table is really a query. So there is something about pulling data from Tables and Queries that is stopping me doing this - if it is just tables, it's OK, even with more than one.
As all the raw data is in the three tables, pretty much everything else I will be doing will be with Queries, so it's quite important to be able to fix this.
If I exclude the Customers table from the Query, it works - I can edit the data in Grid view.
As I said in the OP, the Customers table is really a query. So there is something about pulling data from Tables and Queries that is stopping me doing this - if it is just tables, it's OK, even with more than one.
As all the raw data is in the three tables, pretty much everything else I will be doing will be with Queries, so it's quite important to be able to fix this.
LibreOffice 4.1.1.2 on Windows 8
Re: Updating records from a query again
Are you including all the primary keys from the tables used by the Customers query? I'm not sure if that will work, but it's worth a try.
The other option is to integrate the two queries into one. Are you working with the SQL at all, or making the queries in design view only? You might be forced to use SQL to merge them into one query. If you need some pointers with the SQL, you can go to the list of queries in Base, right click and choose "Edit in SQL view", and post the SQL code for each query here. We could then show you what code to use to get the final result set in one go.
The other option is to integrate the two queries into one. Are you working with the SQL at all, or making the queries in design view only? You might be forced to use SQL to merge them into one query. If you need some pointers with the SQL, you can go to the list of queries in Base, right click and choose "Edit in SQL view", and post the SQL code for each query here. We could then show you what code to use to get the final result set in one go.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Thanks for joining in, MTP! I am in awe of you database people...
At this point it is worth me explaining why I have to use the Customers query to get the results I want, and not a table. I sell coins, and quite often the same people buy coins from me that I buy coins from, so there's no point in me having a Customers table, and a Suppliers table separately, as a lot of the same people would be in them.
So, I have a PEOPLE table. Each person in the table has a Unique ID, Autonumbered by Base, and that is the PK; PEOPLE_ID. In the COINS table, as shown in the graphic, every coin has a DEALER_ID, and that is linked in the Relationships pane, with the PK of the PEOPLE table. Many coins to one Dealer.
In addition, some, but not all, of the coins in the COINS table have been sold, so they will have a CUST_ID, which is also the PEOPLE_ID from the PEOPLE table. No Relationship has been set up in the Relationships pane for this one, but a join in the query returns the correct result. I can't use the PEOPLE table in the query I'm trying to write because it would return the DEALER for the coin, not the customer. So I use a query called CUSTOMERS which is basically the entire PEOPLE table. That way I can link the CUST_ID as a join, and avoid the relationship between the COIN and PEOPLE tables.
Hope that makes sense - it's why adding the PK from the PEOPLE table isn't an option - that would just return who I bought the coin from, not who I sold it to!
Queries don't have Primary Keys, do they? Just checking....
At this point it is worth me explaining why I have to use the Customers query to get the results I want, and not a table. I sell coins, and quite often the same people buy coins from me that I buy coins from, so there's no point in me having a Customers table, and a Suppliers table separately, as a lot of the same people would be in them.
So, I have a PEOPLE table. Each person in the table has a Unique ID, Autonumbered by Base, and that is the PK; PEOPLE_ID. In the COINS table, as shown in the graphic, every coin has a DEALER_ID, and that is linked in the Relationships pane, with the PK of the PEOPLE table. Many coins to one Dealer.
In addition, some, but not all, of the coins in the COINS table have been sold, so they will have a CUST_ID, which is also the PEOPLE_ID from the PEOPLE table. No Relationship has been set up in the Relationships pane for this one, but a join in the query returns the correct result. I can't use the PEOPLE table in the query I'm trying to write because it would return the DEALER for the coin, not the customer. So I use a query called CUSTOMERS which is basically the entire PEOPLE table. That way I can link the CUST_ID as a join, and avoid the relationship between the COIN and PEOPLE tables.
Hope that makes sense - it's why adding the PK from the PEOPLE table isn't an option - that would just return who I bought the coin from, not who I sold it to!
Queries don't have Primary Keys, do they? Just checking....
LibreOffice 4.1.1.2 on Windows 8
Re: Updating records from a query again
A Query, just uses data from either, tables and / or views.
How about doing it this way, please.
I would like to see the SQL ( Structured Query Language ) generated by, both, your CURRENT QUERY . . . that is . . . the Query you are building, AND, the SQL from the Query you have already build . . . CUSTOMERS.
Please, follow these steps:
Please, also, include in your response here, the field(s) that comprise the Primary Key(s) any table defined in the Query, CUSTOMERS .
Sliderule
How about doing it this way, please.
I would like to see the SQL ( Structured Query Language ) generated by, both, your CURRENT QUERY . . . that is . . . the Query you are building, AND, the SQL from the Query you have already build . . . CUSTOMERS.
Please, follow these steps:
- Open your OpenOffice / LibreOffice Base File.
- Click on the Queries icon on the left.
- Right click ONCE on the Query . . . CUSTOMERS . . . and . . . from the drop-down menu, choose: Edit in SQL View...
- Copy and paste what you see in the new window here, to this forum . . . the first word should be: SELECT
- Close that window.
- Do the same ( steps 3 - 5 above ), for the other Query you are creating . . . that is . . . the one using Tables: INVENTORY and COINS and the Query: CUSTOMERS .
- This way, I ( or others in the forum ) could easily modify your original Query . . . so . . . you can accomplish your task.
therefore, rather than using a Query ( which is NOT a table ) . . . I will be able to use the code you already have, and, incorporate it, into the Query that will allow you to UPDATE the data.Sliderule wrote:you MUST include in the Visible fields . . . each field that is a part of a Primary Key , for each table.
Please, also, include in your response here, the field(s) that comprise the Primary Key(s) any table defined in the Query, CUSTOMERS .
Sliderule
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again
Thank you very much - you have amazing patience for what must be simple stuff for you...
The first Query, CUSTOMERS, is dead simple:
The PEOPLE table has a PK called PEOPLE_ID
The Query I'm trying to build:
So the problem is that the query CUSTOMERS has no PK, so Base can't let me edit it.
The first Query, CUSTOMERS, is dead simple:
Code: Select all
SELECT * FROM "PEOPLE"
The Query I'm trying to build:
Code: Select all
SELECT "INVENTORY"."INV_ID", "COINS"."COIN_ID", "COINS"."STATUS", "INVENTORY"."DATE", "INVENTORY"."DENOM", "INVENTORY"."VARIETY", "CUSTOMERS"."EBAY_USERNAME" FROM "COINS", "INVENTORY", "CUSTOMERS" WHERE "COINS"."INV_ID" = "INVENTORY"."INV_ID" AND "CUSTOMERS"."PEOPLE_ID" = "COINS"."CUST_ID" AND "COINS"."STATUS" = 'box'
LibreOffice 4.1.1.2 on Windows 8
Re: Updating records from a query again
OK, thank you for posting the above.
Additionally, you could, if you desire, you can SORT ( ORDER ) the output . . . for example . . . by "PEOPLE"."EBAY_USERNAME" .
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.
- Open your OpenOffice / LibreOffice Base file ( *.odb ) .
- On the left, click once on the Queries icon.
- Under Tasks, click on: Create Query in SQL View...
- Copy and paste the following:
Note: above, i added LINE FEEDS, to make it easier to read.Code: Select all
SELECT "INVENTORY"."INV_ID", "COINS"."COIN_ID", "PEOPLE"."PEOPLE_ID", "COINS"."STATUS", "INVENTORY"."DATE", "INVENTORY"."DENOM", "INVENTORY"."VARIETY", "PEOPLE"."EBAY_USERNAME" FROM "COINS", "INVENTORY", "PEOPLE" WHERE "COINS"."INV_ID" = "INVENTORY"."INV_ID" AND "PEOPLE"."PEOPLE_ID" = "COINS"."CUST_ID" AND "COINS"."STATUS" = 'box'
- Run the query ( F5 or on the toolbar . . . Run Query icon )
- Now, you should be able to UPDATE, INSERT, DELETE data.
- You can save this new Query to whatever name you desire . . . AND . . . by pressing Toolbar Icon: Switch Design View On/Off . . . you can see what it would have looked like, IF, you were creating this Query . . . using the GUI ( Graphic User Interface ).
Additionally, you could, if you desire, you can SORT ( ORDER ) the output . . . for example . . . by "PEOPLE"."EBAY_USERNAME" .
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again [Solved]
Sliderule, you are a genius. Every time I tried to do that with the GUI it returned who I'd bought the coin from!
I shall have to study your SQL carefully to see how what you did was different to what I was doing.
I think this is the line that counts:
AND "PEOPLE"."PEOPLE_ID" = "COINS"."CUST_ID"
Thank you thank you and thank you again.
I'm sure it won't be the last you hear from me....my clunky old XLS did huge amounts of ridiculous things and got so complex that my old laptop overheated and died - that's why I have to migrate it to something a bit more grown-up.
Declan : )
I shall have to study your SQL carefully to see how what you did was different to what I was doing.
I think this is the line that counts:
AND "PEOPLE"."PEOPLE_ID" = "COINS"."CUST_ID"
Thank you thank you and thank you again.
I'm sure it won't be the last you hear from me....my clunky old XLS did huge amounts of ridiculous things and got so complex that my old laptop overheated and died - that's why I have to migrate it to something a bit more grown-up.
Declan : )
LibreOffice 4.1.1.2 on Windows 8
Re: Updating records from a query again [Solved]
Glad to have helped.
Just as an FYI ( For Your Information ) . . .
Also, I would like to congratulate you on making each table and column / field name . . . all UPPER CASE. That is good. And, using the UNDERSCORE character _ to simulate a space . . . for readability.
If you have not already also done this . . . I strongly recommend, each field in your database . . . that you have defined as VARCHAR . . . you change this in the drop-down list, to VARCHAR_IGNORECASE . The reason is, so, if you search for database content, it ( database back-end . . . HSQL ) will find it, regardless of CASE.
For example, when field, "COINS"."STATUS" is defined as VARCHAR and the content is 'box' . . . if you enter it as 'BOX' . . . it will NOT find it. BUT, if you define the field as VARCHAR_IGNORECASE . . . it would be found.
Sliderule
Just as an FYI ( For Your Information ) . . .
Yes, so you know . . . that line, is the same as in the GUI ( Graphic User Interface ) . . . where . . . you drew the line in your ORIGINAL Query, relating ( as in Relational Database ) the fields, "Coins"."CustID" and "Customers"."PeopleID" .Declan wrote:I think this is the line that counts:
AND "PEOPLE"."PEOPLE_ID" = "COINS"."CUST_ID"
Also, I would like to congratulate you on making each table and column / field name . . . all UPPER CASE. That is good. And, using the UNDERSCORE character _ to simulate a space . . . for readability.
If you have not already also done this . . . I strongly recommend, each field in your database . . . that you have defined as VARCHAR . . . you change this in the drop-down list, to VARCHAR_IGNORECASE . The reason is, so, if you search for database content, it ( database back-end . . . HSQL ) will find it, regardless of CASE.
For example, when field, "COINS"."STATUS" is defined as VARCHAR and the content is 'box' . . . if you enter it as 'BOX' . . . it will NOT find it. BUT, if you define the field as VARCHAR_IGNORECASE . . . it would be found.
Sliderule
-
- Posts: 11
- Joined: Sat Sep 28, 2013 11:39 pm
Re: Updating records from a query again [Solved]
I will do that - the Ignore_case thing - good tip. All tips gratefully received!
I have already run up against the next problem but I will try and fix it myself before I come crying to you...all part of the learning process.
I have already run up against the next problem but I will try and fix it myself before I come crying to you...all part of the learning process.
LibreOffice 4.1.1.2 on Windows 8
-
- Posts: 1
- Joined: Thu Oct 22, 2015 12:19 pm
Re: [Solved] Updating records from a query again
Hello guys,
I have the same situation here and although I included the PKs I am still unable to update the records. My situation, I think, is a little bit different.... My database is a postgresql database, instead of an embedded DB. would this solution still work form me?
Thank you,
I have the same situation here and although I included the PKs I am still unable to update the records. My situation, I think, is a little bit different.... My database is a postgresql database, instead of an embedded DB. would this solution still work form me?
Thank you,
Open Office 4.2.8.2 on Ubuntu Linux 14.04
Re: [Solved] Updating records from a query again
Welcome to the OpenOffice / LibreOffice Base Forum.rodrigorrm wrote:Hello guys,
I have the same situation here and although I included the PKs I am still unable to update the records. My situation, I think, is a little bit different.... My database is a postgresql database, instead of an embedded DB. would this solution still work form me?
Thank you,
I do not have a PostGreSQL database, therefore, I cannot test it. But, just to make sure, for each of the tables in your Query ( I am assuming you are working with/from a Query ) . . . will OpenOffice/LibreOffice Base allow you to add records to them? What I am asking, is a confirmation, the user you have defined, do they have the authority to change/modify/update the database contents? Furthermore, does PostGreSQL support updates, from a Query, when the Query is defined from multiple tables?
Sliderule