Operation must use an updatable query (is my logic correct)

[AnExpertNovice]

Trying to update a table with data from another table. The source table is
complex because the effective and termination dates may overlap. In some
cases the first record is needed (at time of incident) and in some cases the
last record is needed (current situation). These dates are needed for
selection only from the source table.

Proper selection requires two queries (1: sort and select 2: a group on key
fields and either a first or last on non-key fields). The resulting query
creates a 1:1 relationship with the table to be updated based on a single
field which is the table's primary key field.

Of course the source query is not updateable because of the GROUP so it
appears that it is unable to be used to update the table.

Must a table be created just to update the first table?
If so, is it because the non-updateable query *MIGHT* create a 1:M
relationship?


Here is an abbreviated (table and field names have been shortened) version
of the query.
(of course the field named qry.key is not really a key but is the same as
tbl.key)

UPDATE tbl
INNER JOIN qry
ON tbl.key = qry.key
SET tbl.objfld = qry!srcfld;

 

[Michel Walsh]

HI,


If you want to update with an aggregate, you can try to use DMax( ), as
example, or, in case of a min or a max, try something like:


UPDATE a INNER JOIN b ON a.itemID=b.itemID
SET a.f1 = b.f2
WHERE b.f2=( SELECT Max(f2) FROM b As bb WHERE
bb.itemID=b.itemID )


That clearly does not work for a SUM, or a COUNT (where you have to use DSum
or DCount, ... or make a table first).



Hoping it may help,
Vanderghast, Access MVP

 

[AnExpertNovice]

Thanks Mike,

I had printed off a thread where John Spencer suggested this a couple of
months ago. Your example made it easy to implement.

With a table of 7,000 entries and a query of 4,600 records with 3,800 1:1
matches, it took over two minutes to execute.

OK. I will probably use a table and queries or open a recordset and update
with code instead. Still, you helped me to learn and to see the
alternative.

I'm still curious why with a 1:1 match why a table can not be updated just
because the source (not to be updated) is not updatable. After finding out
why I will probably utter some words like "that is OBVIOUS" but until then
I'm uttering other words.

Thanks again. You helped a lot.

 

[Michel Walsh]

Hi,


Every language stops somewhere its "optimization" or "analyze of
exceptions". Sure, there is generally no problem to know how to update a
record with (SELECT SUM( ...) ... ) when we can update the same record with
DSum(... ), or even to update a UNION ALL query (since we just merge all the
records, without removing any single one), but the developers have just stop
before the line where those features would have been incorporated (out of
budget, out of time, or more probably, it involves some "out of will").
Microsoft listen to what their customers ask, but some occasion, it takes
more time than for other. You know where is the "suggestion box" from the
Microsoft site (under contact us) is, don't you?




Vanderghast, Access MVP

 

[AnExpertNovice]

hehe My suggestion had been properly filed.

Thanks. With that information and your help I am using now using the most
appropriate method and can move on to my next learning curve issue.

Thanks