UPDATE from Table

[marsss]

Is it possikble in Access SQL query to update a table from data in another like :

UPDATE table1
SET table1.value = table2.value2
FROM table2
WHERE table1.value3 = table2.value2;

 

[Trendsetter]

Just to test it out I tried the principle on a database and it worked fine - this is the SQL

UPDATE tblClient INNER JOIN tblContacts ON tblClient.ID = tblContacts.ClientID SET tblClient.Notes = [tblContacts].[CNotes]
WHERE (([tblClient].[OwnerName]=[tblContacts].[ContactName]));

I used the query builder it is simpler and gives you prompts when you go wrong...

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[marsss]

Thx for answer Trend, finaly it s litle more complicated heh.

I need get Data from another query finaly, which using Sum operator.

Since doing a INNER JOIN between the table and my query, it wont work case it say it can t update value in a query.

And i can t see how to add my query directly in that one cause of this Sum..

there is sample of the sub query and the one who should update

Sub Query

SELECT a.ID, a.Name, Sum(b.value1+(1.5*b.value2)-b.value3) AS TotalFromB
FROM tbl1 AS a INNER JOIN tbl2 AS b ON a.ID=b.ID
GROUP BY a.ID, a.Name;

Update query (doesn t work cause we can t update a query)

UPDATE tbl1 INNER JOIN SubQuery ON tbl.ID = SubQuery.ID
SET tbl.Total = SubQuery.TotalFromB;

Tryed putting Sum into the update, but can t fit my ORDER BY in it.. any idea?

 

[PHV]

Storing calculated/derived values is rarely a good idea as you may retrieve the (accurate) value on the fly with a query.
Anyway, If you insist on denormalizing your database you may use a temporary table (made with the sub query) in your update.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[marsss]

Well, the thing i want do is update all total in a table. Which is calculed from value in another table. But when editing that other other table doesn t update it automatic.

Exemple :

Table 1 (ID, name, total)

Table 2 (ID, value1, value2, value3)

And table1.total = value1+value*1.5-value3

People having access to table1 can t have access to table 2, that s why value aren t keep in it.


Is it bad to make a query to do that, should i go in another way? Like use VBA code instead and run that in a FOR or something?

 

[lespaul]

any idea?

yes, don't store calculated fields in your tables, breaks normalization rules.

Read more at 'The Fundamentals of Relational Database Design'

Leslie

 

[PHV]

table1.total = value1+value*1.5-value3
UPDATE table1 INNER JOIN tble2 ON table1.ID=table2.ID
SET table1.total = value1+value2*1.5-value3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[marsss]

If you insist on denormalizing your database you may use a temporary table"

By temporary table, do u mean something like :

UPDATE tbl1 AS a SET a.total = (SELECT Sum(b.value1+(1.5*b.value2)-b.value3)
FROM tbl2 AS b
WHERE b.ID=a.ID
GROUP BY a.ID);

(this one still isn t working, keep saying that can t be edit)

Or make physicly temp table in database?

 

[marsss]

table1.total = value1+value*1.5-value3
UPDATE table1 INNER JOIN tble2 ON table1.ID=table2.ID
SET table1.total = value1+value2*1.5-value3

My bad, forgot to put the Sum, there is more than one line in table with same ID, more like

table1.total = Sum(value1+value2*1.5-value3)
Having same ID

 

[Trendsetter]

Quote:
Which is calculed from value in another table. But when editing that other other table doesn't update it automatic.

You have stated a primary reason NOT to store calculated values in a table


By the way: How do you activate the inset box as in your recent response Quote: any ideas!


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[marsss]

any idea?

yes, don't store calculated fields in your tables, breaks normalization rules.

Read more at 'The Fundamentals of Relational Database Design'
Leslie

How to give access to the Sum from value in second table to people having access only on first table then?

 

[marsss]

By the way: How do you activate the inset box as in your recent response Quote: any ideas!

use "[ quote ]" and "[ /quote ]

 

[Trendsetter]

Thanks! just that easy eh?

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[lespaul]

How to give access to the Sum from value in second table to people having access only on first table then?

Do you mean security settings? So, people who don't have access to the details in the table can see a summary of the information?



Leslie

 

[marsss]

Yes Leslie