update query not updateable

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

Hi...I'm having trouble understanding how to update a
query properly.

Given:

customer table
jobs table

I am counting the jobs per customer OK. But, I wish to
post that count in the customer table jobs count field.
Help indicates that is not do-able.

I only wish to count this and post the count to the
client record once. I am then incrementing that count
each job entry.

What are my alternatives?

Thanks,
Jen
 
OK...but that does not help me.

I have no clue what approach to take to "Let it work as
designed."

Please provide more.
 
.... and I'm thinking I'd rather store it due to how I am
using it elsewhere.

Can you tell me how?
 
It is seldom a good idea, in a relational database, to store the result of a
calculation. The count of the number of jobs, in your application, is just
such a calculation. If you can calculate the count of jobs for a given
customer in one query, then you can do the same thing in another query (or
the same one) any time you want to.

On the other hand, if you store that value in your customer table, you will
not only have to come up with a way to insert it the first time, but will
also have to have a method for updating it every time a customer has a new
job. Using your query to count the records over and over solves the problem.

In your other post you said, "... and I'm thinking I'd rather store it due
to how I am
using it elsewhere." As I indicated, you can calculate that count ANY TIME
you need it, regardless of where you are using it.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Take a look at the DCOUNT function. You can use that in an update query.

UPDATE SomeTable
Set SomeTable.SomeField =
DCOUNT("*","SomeOtherTable","AFieldInSomeOthertable = " & SomeTable.PrimaryKey)

But as mentioned elsewhere in this thread, this is usually (not always) a bad
idea.
 
Back
Top