update from a count query

  • Thread starter Thread starter mcnews
  • Start date Start date
M

mcnews

can i update a field in a table by using the result of a count query?
and can i do this for all of the rows in the table?
if so, example please.

tia,
mcnewsxp
 
Why would you want to? It's seldom a good idea to store calculated values.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
 
can i update a field in a table by using the result of a count query?
and can i do this for all of the rows in the table?
if so, example please.

tia,
mcnewsxp
Click to expand...

You can... but you shouldn't, usually.

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 have a VERY good reason to do so, and can deal with the risk of update
anomalies, you can use DCount() to do the counting and include it in an Update
query. A Totals query (or any query including a totals query) will not be
updateable.

John W. Vinson [MVP]
 
i guess i don't understand how to do that for each row on the form.
Click to expand...

I have no idea either, because I don't know anything about the structure of
your table, what you're counting, and what you want updated to what. Care to
give us some more information?

John W. Vinson [MVP]
 
I have no idea either, because I don't know anything about the structure of
your table, what you're counting, and what you want updated to what. Care to
give us some more information?
Click to expand...

you can take a look at my previous post re: displaying a counted
value.
 
you can take a look at my previous post re: displaying a counted
value.
Click to expand...

You're not the only person posting here, mcnews. I read hundreds of newsgroup
messages a day in eight newsgroups. You're asking me to fire up Google Groups
and do a search for your previous message; sorry, I'm not going to take the
extra time.

Please feel free to repost your question.

John W. Vinson [MVP]
 
You're not the only person posting here, mcnews. I read hundreds of newsgroup
messages a day in eight newsgroups. You're asking me to fire up Google Groups
and do a search for your previous message; sorry, I'm not going to take the
extra time.

Please feel free to repost your question.
Click to expand...

i don't mean to put you out.......
the post i was referring to is pretty close to this one so i thought
you'd notice it.
here it is:
i have a continuous form that is bound to a distinct row view that
populates some of the fields.
two of the fields must consist of a record count from one of the
tables in the view.
based on the key field of the view the count looks for a value in one
of the fields in the under-lying table.
i would like to know the best way to get this count and display it in
the field of the continuous form.
so the row looks something like:

KeyID DateRcvd Description CountOfRowsForKeyID1
CountOfRowsForKeyID2 Unique Comments RprtDat

i also need to scan a 20 character comment field in the same table and
display the unique comments in one larger field.

i am wondering if i need to do some preprocessing to store all of this
in a temp table whenever the form is opened.

i'd appreciate any ideas on this one.
 
Back
Top