Update one table with another table field value

[jgitaunjoroge]

Hi All,

Am trying to update one table with another tables value where both tables have a common relationship.
my code is a below.

DoCmd.RunSQL "update customer set customer.paid" = totalprojsumpaid.AMOUNT where "customer.ACCNO = totalprojsumpaid = ACCNO"
 

Note the two fields calculated fields.

Thanks,
Njoroge Joseph

[strive4peace]

perhaps this?

dim sSQL as string
ssql = "UPDATE Customer C INNER JOIN totalprojsumpaid TP " _
& " ON C.ACCNO = TP.ACCNO " _
& " SET C.Paid = TP.Amount"
 
currentdb.execute sSQL

This will do all records since there is no criteria for a specific account

[jgitaunjoroge]

Strive4peace,

Please note when i run the code it returns Two few parameters. Expected 1

Customer.paid is the main table being update by totalprojsumpaid.amount
the criteria is Customer.accno=txtaccno must be same with totalprojsumpaid.accno as it is in relationship. txtaccno is a text field recording accno for both tables.

[strive4peace]

your error message suggests that something is not filled out. What is the SQL for totalprojsumpaid ? (am assuming it must be a query).

Perhaps there is a better way to update the paid field in your customers table.

[zmbd]

jgitaunjoroge,

What we should understand first is why you are doing this in the first place. A normalized database most likely would not need such an update between tables.

[strive4peace]

sorry to disagree, but not necessarily. If methods are in place that can be run anytime, storing some calculated fields in favor of better performance is ok, in my opinion. I know not everyone agrees with this but I have seen many databases that took a long time to calculate -- not because they weren't designed properly, but because there was so much data.

[zmbd]

S4P:
Certainly there are times when such things are needed - agreed; however, I am not sure why you would disagree with my last post in it's entirety, in as much as it is an inquiry regarding the basis for OP's goal - in which I noted that most likely such things are not needed within a properly normalized database - not that there is never a need... to whit: Is it for an audit trail, a one time discounted price, or as you've noted - a prefetch/calc for efficiency on a large dataset (I've done this myself in temp tables upon occasion :) )

IMHO, once we have a better understanding of what OP is trying to accomplish we might be able to point him towards a solution to the problem or maybe a better method of handling the data. Indeed, we may need a bit more detail too before the picture clears up for us. :)

[strive4peace]

sorry, it just seemed a little rough to me .... I agree that a better understanding would help.

[zmbd]

strive4peace, if occasionally something seems/appears to be harsh, please keep in mind that many of my shorter posts are along the line of a "text-message" - devoid of body language - short - concise. I work in a lab and all too often these post are made in the 5 minute break between tests and my normal data-entry and DBA tasks; thus, I need to pack a lot of information in a very short time frame....

bright side... I've learned to touch type at a rate I would never have thought possible :)

[strive4peace]

Thanks. I have read some of your posts, zmbd, and most are very good (must be that scientist way of thinking). Oftentimes, alleviating frustration with comforting words gives others a way to see the answer for themselves. Access is hard to learn. I applaud those who try.