[Solved] Updating records from a query again

Creating tables and queries
Post Reply
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

[Solved] Updating records from a query again

Post by declanwmagee »

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:
BoxQuery.png
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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Updating records from a query again

Post by Sliderule »

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. :bravo:

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 :super: ) . . . the fields that comprise Primary Key(s) include:
  1. "Inventory"."InvID"
  2. "Coins"."CoinID"
Therefore, if you INCLUDE in your Query, the above two fields . . . OpenOffice / LibreOffice Base will allow you to:
  1. INSERT ( add ) new records
  2. UPDATE ( change ) table content
  3. DELETE ( remove ) records
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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 : )
LibreOffice 4.1.1.2 on Windows 8
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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!
LibreOffice 4.1.1.2 on Windows 8
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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! :(
LibreOffice 4.1.1.2 on Windows 8
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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....
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
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

OK, back to Square One. New dB, new tables, new query:

Image

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:

Image

not in a form or anything, just the data.

Any ideas?
LibreOffice 4.1.1.2 on Windows 8
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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.
LibreOffice 4.1.1.2 on Windows 8
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Updating records from a query again

Post by MTP »

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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

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....
LibreOffice 4.1.1.2 on Windows 8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Updating records from a query again

Post by Sliderule »

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:
  1. Open your OpenOffice / LibreOffice Base File.
  2. Click on the Queries icon on the left.
  3. Right click ONCE on the Query . . . CUSTOMERS . . . and . . . from the drop-down menu, choose: Edit in SQL View...
  4. Copy and paste what you see in the new window here, to this forum . . . the first word should be: SELECT
  5. Close that window.
  6. 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 .
  7. This way, I ( or others in the forum ) could easily modify your original Query . . . so . . . you can accomplish your task. :bravo:
Bottom line . . . just so you understand, as I stated my FIRST post to you here,
Sliderule wrote:you MUST include in the Visible fields . . . each field that is a part of a Primary Key , for each table.
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.

Please, also, include in your response here, the field(s) that comprise the Primary Key(s) any table defined in the Query, CUSTOMERS .

Sliderule
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again

Post by declanwmagee »

Thank you very much - you have amazing patience for what must be simple stuff for you...

The first Query, CUSTOMERS, is dead simple:

Code: Select all

SELECT * FROM "PEOPLE"
The PEOPLE table has a PK called PEOPLE_ID

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'
So the problem is that the query CUSTOMERS has no PK, so Base can't let me edit it.
LibreOffice 4.1.1.2 on Windows 8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Updating records from a query again

Post by Sliderule »

OK, thank you for posting the above.
  1. Open your OpenOffice / LibreOffice Base file ( *.odb ) .
  2. On the left, click once on the Queries icon.
  3. Under Tasks, click on: Create Query in SQL View...
  4. Copy and paste the following:

    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'
    Note: above, i added LINE FEEDS, to make it easier to read.
  5. Run the query ( F5 or on the toolbar . . . Run Query icon )
  6. Now, you should be able to UPDATE, INSERT, DELETE data.
  7. 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 ).
Explanation: By adding the table "PEOPLE" and the primary key for that table "PEOPLE"."PEOPLE_ID", to the Query . . . you have now met the requirements of each table, and, the primary key(s), in the SELECT clause.

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.
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again [Solved]

Post by declanwmagee »

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 : )
LibreOffice 4.1.1.2 on Windows 8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Updating records from a query again [Solved]

Post by Sliderule »

Glad to have helped.

Just as an FYI ( For Your Information ) . . .
Declan wrote:I think this is the line that counts:
AND "PEOPLE"."PEOPLE_ID" = "COINS"."CUST_ID"
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" . :crazy:

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. :bravo:

Sliderule
declanwmagee
Posts: 11
Joined: Sat Sep 28, 2013 11:39 pm

Re: Updating records from a query again [Solved]

Post by declanwmagee »

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.
LibreOffice 4.1.1.2 on Windows 8
rodrigorrm
Posts: 1
Joined: Thu Oct 22, 2015 12:19 pm

Re: [Solved] Updating records from a query again

Post by rodrigorrm »

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,
Open Office 4.2.8.2 on Ubuntu Linux 14.04
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Updating records from a query again

Post by Sliderule »

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,
Welcome to the OpenOffice / LibreOffice Base Forum.

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
Post Reply