Update Query based on criteria (joins)

[kmhnhsuk]

Hi there,
I have two tables one for Fixed Assets called tblFixedAssetRegister and one
for depreciation called tblDeprnTotal. The table called tblDeprnTotal is the
result of a 'Make Table Query' that basically calculates the depreciation for
the period and gives a total for each asset so far. This table will be
deleted and re-created each month.

I basically want a way of updating tblFixedAssetRegister with the total
depreciated so far for each asset. I have a field within the
tblFixedAssetRegister to hold these details. The unique identifier in
tblFixedAssetRegister is called 'Asset Number' and the same id in
tblDeprnTotal is called 'Reference'.

I have created an update query to perform the update of
tblFixedAssetRegister with the 'Depreciated So Far' but it does not appear to
work. Access prompts for 2 parameters - one called
tblFixedAssetRegister.[Asset Number] and the other is tblDeprnTotal.Credit

Here is the code for the update query:

UPDATE tblFixedAssetRegister SET tblFixedAssetRegister.[Depreciated So Far]
= [tblDeprnTotal].[Credit]
WHERE ((([tblDeprnTotal].[Reference])=[tblFixedAssetRegister].[Asset
Number]));

Can anyone help?

 

['69 Camaro]

Hi.

Storing a calculated value in a table (even if it's updated only once a
month) is generally hazardous to data integrity. However, if you have
safeguards in place that guarantee that the depreciation calculations are
always up-to-date and are always applied on a timely basis, then you'll need
to join the two tables in the update query. You need unique indexes that
won't alllow NULL's created for each of the Asset Number and the Reference
fields. A primary key on each of these fields is ideal.

UPDATE tblFixedAssetRegister INNER JOIN tblDeprnTotal ON
tblFixedAssetRegister.[Asset Number] = tblDeprnTotal.Reference
SET tblFixedAssetRegister.[Depreciated So Far] = tblDeprnTotal.Credit;

If you are prompted for any of these field names as parameters for the
query, then the query isn't spelling the field name correctly or the field
doesn't exist in the designated table, so you'll need to check these things.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi there,
I have two tables one for Fixed Assets called tblFixedAssetRegister and one
for depreciation called tblDeprnTotal. The table called tblDeprnTotal is the
result of a 'Make Table Query' that basically calculates the depreciation for
the period and gives a total for each asset so far. This table will be
deleted and re-created each month.

I basically want a way of updating tblFixedAssetRegister with the total
depreciated so far for each asset. I have a field within the
tblFixedAssetRegister to hold these details. The unique identifier in
tblFixedAssetRegister is called 'Asset Number' and the same id in
tblDeprnTotal is called 'Reference'.

I have created an update query to perform the update of
tblFixedAssetRegister with the 'Depreciated So Far' but it does not appear to
work. Access prompts for 2 parameters - one called
tblFixedAssetRegister.[Asset Number] and the other is tblDeprnTotal.Credit

Here is the code for the update query:

UPDATE tblFixedAssetRegister SET tblFixedAssetRegister.[Depreciated So Far]
= [tblDeprnTotal].[Credit]
WHERE ((([tblDeprnTotal].[Reference])=[tblFixedAssetRegister].[Asset
Number]));

Can anyone help?

 

[John Vinson]

Hi there,
I have two tables one for Fixed Assets called tblFixedAssetRegister and one
for depreciation called tblDeprnTotal. The table called tblDeprnTotal is the
result of a 'Make Table Query' that basically calculates the depreciation for
the period and gives a total for each asset so far. This table will be
deleted and re-created each month.

I basically want a way of updating tblFixedAssetRegister with the total
depreciated so far for each asset. I have a field within the
tblFixedAssetRegister to hold these details. The unique identifier in
tblFixedAssetRegister is called 'Asset Number' and the same id in
tblDeprnTotal is called 'Reference'.

You need to update based on a query Joining the two tables. Try

UPDATE tblFixedAssetRegister
INNER JOIN tblDeprnTotal
ON [tblDeprnTotal].[Reference] = [tblFixedAssetRegister].[Asset
Number]
SET tblFixedAssetRegister.[Depreciated So Far]
= [tblDeprnTotal].[Credit];

This assumes that [Asset Number] has a unique Index in
tblFixedAssetRegister.

John W. Vinson[MVP]