multiple row update

[polilop]

Is it possible to update multiple rows?
I have two tables. first table named Values has theses columns (and values):
ID Value SumID
1 100 5
2 44 5
3 214 6
4 50 6
The second table named Sums has these values
ID Sum
5 144
6 264

The Sums table holds the sum of all the values from the value table that
have the same SumID.
When i change the values in the Value table i need to update the Sum's. Is
it possible to do this with a query in access?

 

[Dirk Goldgar]

Is it possible to update multiple rows?
I have two tables. first table named Values has theses columns (and
values): ID Value SumID
1 100 5
2 44 5
3 214 6
4 50 6
The second table named Sums has these values
ID Sum
5 144
6 264

The Sums table holds the sum of all the values from the value table
that have the same SumID.
When i change the values in the Value table i need to update the
Sum's. Is it possible to do this with a query in access?

Well, sort of. If the Sums table is always supposed to contain the sums
the Values table, then there's no need or reason to have that table at
all. A simple totals query will give you that. The SQL of such a query
would be:

SELECT SumID As ID, Sum(Value) As [Sum]
FROM [Values]
GROUP BY SumID;

If you create and save this query under the name "Sums", you can use
"Sums" anywhere you'd use a table. The results of the query won't be
updatable, of course, because they're calculated; however, it sounds
like you want them to always reflect the sums of what's in the Values
table, in which case they *shouldn't* be updatable directly.

 

[polilop]

Is it possible to update multiple rows?
I have two tables. first table named Values has theses columns (and
values): ID Value SumID
1 100 5
2 44 5
3 214 6
4 50 6
The second table named Sums has these values
ID Sum
5 144
6 264

The Sums table holds the sum of all the values from the value table
that have the same SumID.
When i change the values in the Value table i need to update the
Sum's. Is it possible to do this with a query in access?

Well, sort of. If the Sums table is always supposed to contain the sums
the Values table, then there's no need or reason to have that table at
all. A simple totals query will give you that. The SQL of such a query
would be:

SELECT SumID As ID, Sum(Value) As [Sum]
FROM [Values]
GROUP BY SumID;

If you create and save this query under the name "Sums", you can use
"Sums" anywhere you'd use a table. The results of the query won't be
updatable, of course, because they're calculated; however, it sounds
like you want them to always reflect the sums of what's in the Values
table, in which case they *shouldn't* be updatable directly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Still dosen't answer my question about possibility to update multiple rows.
1. Let's sey i need to update the sums table?
2. If not possible can i create a table with the name sums, and insert the
values i got from the select into it?

 

[Dirk Goldgar]

Is it possible to update multiple rows?
I have two tables. first table named Values has theses columns (and
values): ID Value SumID
1 100 5
2 44 5
3 214 6
4 50 6
The second table named Sums has these values
ID Sum
5 144
6 264

The Sums table holds the sum of all the values from the value table
that have the same SumID.
When i change the values in the Value table i need to update the
Sum's. Is it possible to do this with a query in access?

Well, sort of. If the Sums table is always supposed to contain the
sums the Values table, then there's no need or reason to have that
table at all. A simple totals query will give you that. The SQL of
such a query would be:

SELECT SumID As ID, Sum(Value) As [Sum]
FROM [Values]
GROUP BY SumID;

If you create and save this query under the name "Sums", you can use
"Sums" anywhere you'd use a table. The results of the query won't be
updatable, of course, because they're calculated; however, it sounds
like you want them to always reflect the sums of what's in the Values
table, in which case they *shouldn't* be updatable directly.

Still dosen't answer my question about possibility to update multiple
rows.
1. Let's sey i need to update the sums table?
2. If not possible can i create a table with the name sums, and
insert the values i got from the select into it?

If you really must have this table, then you need to delete old records
from it and put new records into it, based on the totals query, whenever
you update the Values table. There is nothing in Access that will make
this happen automatically, but you can put code behind a form to do it.
It isn't clear to me whether you are talking about replacing *all* the
records in the Sums table, or just some of them. A wholesale update of
the Sums table could be accomplished by two successive action queries:

DELETE * FROM Sums;

INSERT INTO Sums(ID, [Sum])
SELECT SumID As ID, Sum(Value) As [Sum]
FROM [Values]
GROUP BY SumID;

These SQL statements could easily be executed in code.

 

[George Nicholson]

Still dosen't answer my question about possibility to update multiple

rows.
1. Let's sey i need to update the sums table?

AFAIK, you can't update two separate tables with the same update query, so
no, it isn't possible.
You would need 2 separate update queries, one for each table.

2. If not possible can i create a table with the name sums, and insert the
values i got from the select into it?

Once you have updated the Values table, you could do any one of the
following:
1) use a 2nd update query to update the Sum field in Sums
2) Delete the existing Sums table and then use a make table query to
recreate Sums in its entirety, based on the new contents of Values
3) Delete the existing contents of Sums and use an append query to
repopulate it, based on the new contents of Values
4) as Dirk suggests, eliminate the Sums table and simply use a saved
query ("Sums") in its place. This way you would only have to worry about
keeping the Values table updated and you won't be violating one of the
cardinal rules of good database design: never store calculated values.
(Although there are times when it is appropriate to break this rule, they
are pretty rare. One of the consequences of breaking this rule, whether the
reasons are valid or not, is the extra work required to ensure data
integrity.)


HTH,
--
George Nicholson

Remove 'Junk' from return address.

Is it possible to update multiple rows?
I have two tables. first table named Values has theses columns (and
values): ID Value SumID
1 100 5
2 44 5
3 214 6
4 50 6
The second table named Sums has these values
ID Sum
5 144
6 264

The Sums table holds the sum of all the values from the value table
that have the same SumID.
When i change the values in the Value table i need to update the
Sum's. Is it possible to do this with a query in access?

Well, sort of. If the Sums table is always supposed to contain the sums
the Values table, then there's no need or reason to have that table at
all. A simple totals query will give you that. The SQL of such a query
would be:

SELECT SumID As ID, Sum(Value) As [Sum]
FROM [Values]
GROUP BY SumID;

If you create and save this query under the name "Sums", you can use
"Sums" anywhere you'd use a table. The results of the query won't be
updatable, of course, because they're calculated; however, it sounds
like you want them to always reflect the sums of what's in the Values
table, in which case they *shouldn't* be updatable directly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Still dosen't answer my question about possibility to update multiple
rows.
1. Let's sey i need to update the sums table?
2. If not possible can i create a table with the name sums, and insert the
values i got from the select into it?