Microsoft programmers are always very particular on their way of approaching problems. Since they are the “biggest” software developers in the world, they have to set the standards!?
Problem: Updating field(s) of a table using a complex (sub) query.
Solution:
1. In standard SQL, you can this:
UPDATE TABLE1
SET A_FIELD = Q.CALCULATED_FIELD
FROM A_COMPLEX_QUERY Q
WHERE TABLE1.ID_FIELD = Q.ID_FIELD;
It makes sense, right?
No, not according to MS Access!
2. You must try something like this:
UPDATE TABLE1
INNER JOIN A_COMPLEX_QUERY Q
ON TABLE1.ID_FIELD = Q.ID_FIELD
SET A_FIELD = Q.CALCULATED_FIELD;
However, it says:
“You must use an updatable query!”
3. One of the “good” features of MS Access is its functions.
UPDATE TABLE1
SET A_FIELD =
DLOOKUP("CALCULATED_FIELD", "A_COMPLEX_QUERY", "ID_FIELD = " & ID_FIELD);
Simple; but it may take ages execute if you have thousands of records.
You can also write your own (public) functions in VBA modules and refer to them in anywhere inside your MS Access database.
Categories: