update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables. One has fields "program" and "pvalue". The other has
"program2", "pdate" and "pdvalue".

I have to sum the values for program2 and update the pvalue.

I have tried numerous approaches.

Would you have a solution for this dilemma?

TIA
 
Hi Ria,

I would suggest to 2 queries:
1. Query on Table2 (incl. Program2, Pdate and Pdvalue) : Select the fields
Program2 and Pdvalue, add the Sum row by clicking on the Sum icon on top,
select 'Group by' for Program2 and 'Sum' for Pdvalue. This will give you the
sum of all Pdvalues for each Program2.

2. Update Query with both tables included, link Table1!Program with
Table2!Program2. Select Pvalue from Table1 and enter in the 'Update to' row
the Table2!Pdvalue.

That's it.

Here the SQL for the queries:
1)
SELECT Table_2.program2, Sum(Table_2.pdvalue) AS SumOfpdvalue
FROM Table_2
GROUP BY Table_2.program2;

2)
UPDATE Table_1 INNER JOIN Q1 ON Table_1.program = Q1.program2 SET
Table_1.pvalue = [q1]![sumofpdvalue];

HTH
Bernd
 
Hi,

looks like that i forgot something. Access doesn't like it when the source
in an Update query is another query. Therefore you will have to create a 3rd
table with the calculated figures from Query1 and use this new table as
source in the update query.

Here the amended SQL:
1)
SELECT Table_2.program2, Sum(Table_2.pdvalue) AS SumOfpdvalue INTO Table_3
FROM Table_2
GROUP BY Table_2.program2;

2)
UPDATE Table_1 INNER JOIN Table_3 ON Table_1.program = Table_3.program2 SET
Table_1.pvalue = Table_3!sumofpdvalue;


Sorry,
Bernd


BerHav said:
Hi Ria,

I would suggest to 2 queries:
1. Query on Table2 (incl. Program2, Pdate and Pdvalue) : Select the fields
Program2 and Pdvalue, add the Sum row by clicking on the Sum icon on top,
select 'Group by' for Program2 and 'Sum' for Pdvalue. This will give you the
sum of all Pdvalues for each Program2.

2. Update Query with both tables included, link Table1!Program with
Table2!Program2. Select Pvalue from Table1 and enter in the 'Update to' row
the Table2!Pdvalue.

That's it.

Here the SQL for the queries:
1)
SELECT Table_2.program2, Sum(Table_2.pdvalue) AS SumOfpdvalue
FROM Table_2
GROUP BY Table_2.program2;

2)
UPDATE Table_1 INNER JOIN Q1 ON Table_1.program = Q1.program2 SET
Table_1.pvalue = [q1]![sumofpdvalue];

HTH
Bernd

ria said:
I have 2 tables. One has fields "program" and "pvalue". The other has
"program2", "pdate" and "pdvalue".

I have to sum the values for program2 and update the pvalue.

I have tried numerous approaches.

Would you have a solution for this dilemma?

TIA
Click to expand...
Click to expand...
 
Back
Top