Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2019, 04:46 AM   #1
Gismo
Newly Registered User
 
Join Date: Jun 2017
Posts: 323
Thanks: 199
Thanked 1 Time in 1 Post
Gismo is on a distinguished road
Update Value in table

Hi All, please could you help me understand, i have this scenario and not sure whether i am approaching this correct.
I need to update the value in a control in a table by adding a value from another table to the existing value
can I do the addition in an update query form and to the same table or do i need to copy to a temporary table, do the addition and then copy back to the original table?

Gismo is offline   Reply With Quote
Old 04-17-2019, 04:54 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,875
Thanks: 105
Thanked 2,378 Times in 2,186 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Update Value in table

If you can join the 2 tables in your query & the datatypes for each field are identical then you should be able to do it in the query.
Depending on whether its a number or text field, either add the new number value or concatenate the text fields
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Gismo (04-17-2019)
Old 04-17-2019, 05:00 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,228
Thanks: 10
Thanked 2,231 Times in 2,184 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Update Value in table

Quote:
I need to update the value in a control in a table by adding a value from another table to the existing value
Where does the initial value in the first table come from? Is it just the summation of values that exist in other tables?

plog is offline   Reply With Quote
Old Yesterday, 10:25 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,545
Thanks: 13
Thanked 1,448 Times in 1,380 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Update Value in table

Typically we do not store calculated values and I don't see a reason to deviate from that policy here. I would add a second column to the table to hold the additive value so that you can always distinguish it from the original value. Then in a query you would sum the two and on the form, you would have an unbound control that shows the total:

= Nz(fldA,0) + Nz(fldB,0)

Since one of the fields may be null (at least at some point during the data entry, you need to use Nz() to account for that)

Use DLookup() to obtain the additive value and place it in fldB at the point in time where the key to the value is available.

We don't have a lot of detail to work with so that is the best I can do.

PS - the reason for adding the second column is because that allows for manual override and it also allows for changes in value. So if the value to add is $10 today but tomorrow it is changed to $10.50, you want to be able to see the value at the time it was used. If the value "never" changes or you have a way to obtain the correct value at a point in time, then don't store anything. Just join to the lookup table in the query and calculate in the query.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query criteria compare a field in another table and update accordingly AccessBee Queries 11 06-29-2015 03:34 PM
Update from Outlook to Table works but fails to update Form JoyP Forms 4 03-09-2012 02:22 AM
Update Query - Update Duplicate value from Single Matching value from another table xxx Queries 4 06-14-2011 11:28 PM
UPDATE query (not update if certain critea exist in the destination table) homer2002 Queries 6 02-15-2003 02:15 PM
Using and Append (or update) Query to update a table from a linked table. Plim Queries 3 10-17-2001 08:25 PM




All times are GMT -8. The time now is 12:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World