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