Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am fighting with Access to find a way to use Query with aggregate functions
to update table. One hard way is to make table from Query and then update
another table. Another way is to use Dlookup function, but it is very slow.
Am I overlooking an alternate way? or is there a way to speed up Dlookup?
Thanks in advance.
 
I am fighting with Access to find a way to use Query with aggregate functions
to update table. One hard way is to make table from Query and then update
another table. Another way is to use Dlookup function, but it is very slow.
Am I overlooking an alternate way? or is there a way to speed up Dlookup?
Thanks in advance.
Click to expand...

No Aggregate query is ever updateable (even if it logically ought to
be). It's just a limitation of the program.

Is it *ESSENTIAL* to store this calculation? It's rarely a good idea.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you must store it (perhaps this is a case where the calculation is
in fact too slow), you may be able to speed up the domain functions by
indexing the fields used in the criteria, if they don't already have
indexes.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for your responce John:
Absolutely aggree on that store calculations is wrong. That is my
frustration. I have to prepare multicolumn table or query as source of data
sheet form (6 records the user have to view). Calculate columns in real
(using aggregate functions) time is very slow. So, I am exploring shifting
dead time to start application phase. But once it was started, then viewing
records is relatively fast (just view, no entries).

I may still loosing your advise to calculate in form controls (in columns of
data sheet form). I can not construct a single query to satisfy all columns.
But jet engine does not allow update single form source from queries.

So, may be there is a way to use multiple queries as form Record Soursce
Attribute?
Any other suggestion is welcome.
Many thanks.
 
Back
Top