Updating a table field with data from another table

[BaseNoob]

I’m completely new to LibreOffice and Base. I’ve successfully created a database with tables, forms and some simple reports. I’ve learned a lot about relational db’s so far. Now I’m turning to managing the maintenance and developing some more sophisticated reporting.

My current challenge: I need to regularly update a field (“Accounts”.“CurrValue”) with the most recent value found in another table (“BalanceHistory”.“Balance”). The BalanceHistory table has a BalanceDate field that can be checked to determine which record for a given AcctNo has the most recent Balance. The two tables (Accounts and BalanceHistory) are joined by the AcctNo field.

Assuming this is best done with a SQL command, I’m looking for some help writing the SQL for it. Am I on the right track? Volunteers?

Thanks in advance,

BaseNoob

[Ratslinger]

Hello @BaseNoob

It is not typical to duplicate information in a relational database. Since you already have these tables linked, you only need an SQL select to obtain the information anytime it is needed.

However, if you insist on this route, there are some other things to be aware of. To do this you will need to use an SQL UPDATE statement. This cannot be done in the query section. You would need to do this from the main .odb form from the menu Tools->SQL selection. With that, there is no built in storage for this type of statement so you would need to keep a copy of the statement somewhere else like a text file for when needed.

Another method is to do this via a macro. Since you appear to be new to all this, it may be beyond you current knowledge.

Here is a rough statement to do what you asked. This needs modification based on the “BalanceHistory” table as it is unclear as to which record for the Account is to be retrieved for the balance:

UPDATE "Accounts" SET "CurrValue"= (SELECT  "BalanceHistory"."Balance" FROM "BalanceHistory"  WHERE "BalanceHistory"."MYID" = "Accounts"."MYID")

You would need to replace "MYID"s with the respective ID fields in your tables.

EDIT:

Here is a statement to update based upon the latest date in the BalanceHistory table for each different AcctNo (AGAIN: use against advise):

UPDATE "Accounts"
   SET "CurrValue" = (SELECT "Balance"
                      FROM (SELECT "AcctNo",   "Balance"
                            FROM "BalanceHistory",
                                 (SELECT "AcctNo",  MAX("BalanceDate") SELDATE
                                  FROM "BalanceHistory"
                                  GROUP BY "AcctNo") A
                            WHERE "BalanceHistory"."AcctNo" = A."AcctNo"
                            AND   "BalanceHistory"."BalanceDate" = A.SELDATE) B
                      WHERE "Accounts"."AcctNo" = B."AcctNo")

EDIT 9/28/2017:

The previous UPDATE statement bothered me enough to post a much preferred solution which can be retrieved anytime and is not copied into the main ‘Account’ record. Additionally, since it is a query, it allows the statement to be stored in the Query section. Same result using a join in a SELECT statement:

SELECT "Accounts".*, B."Balance" FROM "Accounts"
LEFT JOIN
(SELECT "AcctNo",  "Balance"
FROM "BalanceHistory",
     (SELECT "AcctNo",  MAX("BalanceDate") SELDATE
     FROM "BalanceHistory"
     GROUP BY "AcctNo") A
WHERE "BalanceHistory"."AcctNo" = A."AcctNo"
      AND   "BalanceHistory"."BalanceDate" = A.SELDATE) B
ON "Accounts"."AcctNo" = B."AcctNo"

If this answers your question please click on the (upper left area of answer).

[BaseNoob]

Thanks, Ratslinger for your quick response! That’s a good start for me. I don’t see, though, how this statement finds the most RECENT record to use to update the Accounts table. I mentioned that the BalanceDate field in the BalanceHistory table will determine which record will provide the balance for that AcctNo. I.e., every AcctNo record in the Accounts table will get updated from the BalanceHistory table with its most recent Balance. Can that filter be added to the WHERE?

[Ratslinger]

Forget last now deleted comments - have possible solution. Will post shortly.

[Ratslinger]

OK. Have successfully updated records in one table from another using the balance in the latest dated record. This is a bit more complex SQL statement using MAX to find date. I would highly advise against this method. There are much better ways and means of getting balances. I personally would NEVER use this. Be aware this was transcribed to your field names and may not be perfect. Have triple checked what I can. See edit in answer.

[Ratslinger]

BTW - The SQL is based upon the fact that each record in ‘BalanceHistory’ has unique dates for each account. In other words, no two records with the same date for the same account.

[BaseNoob]

Ratslinger, this is awesome. It worked perfectly! I understand your advice against keeping balances this way, but you can rest assured that we have reasons for maintaining a history of the account balances. That’s why I created a separate table to keep them in.

No problem executing this SQL using the copy-and-paste-the-text method, but were you saying that this SQL statement could be executed with a macro? If so, I’ll start studying up on how to do them. Thanks a ton for help!

[Ratslinger]

I’ve posted a number of examples of this - here is one.

The BalanceHistory is not what bothers me (although I have never heard a GOOD reason). It’s copying the info AGAIN into the other record. A real waste and unnecessary. You can basically do that with an SQL select - almost the one I presented. Also there are too many things which can go wrong with the approach taken.
Anyway, glad it works.