UPDATE QUERY

[ripon via AccessMonster.com]

I have a select quey(q1) as follows:
SELECT MAX(CYR) AS Y1, MAX(CMN) AS M1 FROM TABLE1
Now I am trying to create an UPDATE query(q2) like this:
UPDATE TABLE2
SET TABLE2.MONTH =Q1.M1
WHERE TABLE2.YEAR= Q1.Y1

---I am getting an error. It taking Q1.Y1 as a parameter.
What is the problem.

 

[Van T. Dinh]

You have to include Q1 in the UPDATE Clause. Try something like:

UPDATE TABLE2 INNER JOIN
Q1 On TABLE2.[YEAR]= Q1.Y1
SET TABLE2.[MONTH] =Q1.M1

 

[John Vinson]

I have a select quey(q1) as follows:
SELECT MAX(CYR) AS Y1, MAX(CMN) AS M1 FROM TABLE1
Now I am trying to create an UPDATE query(q2) like this:
UPDATE TABLE2
SET TABLE2.MONTH =Q1.M1
WHERE TABLE2.YEAR= Q1.Y1

---I am getting an error. It taking Q1.Y1 as a parameter.
What is the problem.

You're assuming that you can just mention the name of a query and
expect Access to open and run that query. You can't.

In addition, no Totals query or any query including a Totals query is
ever updateable. You can get around this using the DMax() function:

UPDATE Table2
SET Table2.[Month] = DMax("[CMN]", "[Table1]"), Table2.[Year] =
DMax("[CYR]", "Table1");

Note that this will update EVERY RECORD in Table2, and that if CYR and
CMN are independent month and year fields, the result may pull values
from different records; that is, if there is any record in Table1 from
December (CMN = 12) anywhere in the table, that will be the value
returned by Max/Dmax, whether it's from 2005 or 1872.

Note also that Month and Year are reserved words and dangerous choices
as fieldnames. Access WILL get confused.

Could you explain the nature of these tables, and why you need two
fields rather than a single Date/Time field?

John W. Vinson[MVP]

 

[ripon via AccessMonster.com]

Hi Van:
Thanks its working. Thanks a lot. Let me try to simplify the problem. You may
have a solution to solve it in single UPdate query.

I have a table(t1) like this:
year month
2000 8
2005 7
2005 8
2003 1

I have 2nd table(t2) like this:
year month
2005 7
-Now I have to update month field of t2 with the maximum value of month from
t1 where t1.year=t2.year
Can I do it in single Update query.
------

You have to include Q1 in the UPDATE Clause. Try something like:

UPDATE TABLE2 INNER JOIN
Q1 On TABLE2.[YEAR]= Q1.Y1
SET TABLE2.[MONTH] =Q1.M1

I have a select quey(q1) as follows:
SELECT MAX(CYR) AS Y1, MAX(CMN) AS M1 FROM TABLE1

[quoted text clipped - 5 lines]

---I am getting an error. It taking Q1.Y1 as a parameter.
What is the problem.

 

[ripon via AccessMonster.com]

Hi John:
I have now that is working:
I have a huge central table1a with data like:
year month
2000 12
2005 8
2005 6
2003 5
--2nd table2a has only 1 record that is used for a processing.
year month
2005 7
--here month(7) has to be updated to 8 as per available data in table1a.
Now I have created a select query:
SELECT TOP 1 t1.year AS y1, t1.month AS m1
FROM table1a AS t1
WHERE t1.month in (select max(t2.month) from table1a t2 where t1.year = t2.
year)
ORDER BY year DESC;
--This select actually selecting maximum month from the maximum year in
table1a.(may be you can modify it)
Then I have your Update query:
UPDATE table2a INNER JOIN query3 ON table2a.year=query3.y1 SET table2a.[month]
= query3.m1;
------This updates month 7 to 8 in table2a

Please tell me if any better idea.

--Thanks again

I have a select quey(q1) as follows:
SELECT MAX(CYR) AS Y1, MAX(CMN) AS M1 FROM TABLE1

[quoted text clipped - 5 lines]

---I am getting an error. It taking Q1.Y1 as a parameter.
What is the problem.

You're assuming that you can just mention the name of a query and
expect Access to open and run that query. You can't.

In addition, no Totals query or any query including a Totals query is
ever updateable. You can get around this using the DMax() function:

UPDATE Table2
SET Table2.[Month] = DMax("[CMN]", "[Table1]"), Table2.[Year] =
DMax("[CYR]", "Table1");

Note that this will update EVERY RECORD in Table2, and that if CYR and
CMN are independent month and year fields, the result may pull values
from different records; that is, if there is any record in Table1 from
December (CMN = 12) anywhere in the table, that will be the value
returned by Max/Dmax, whether it's from 2005 or 1872.

Note also that Month and Year are reserved words and dangerous choices
as fieldnames. Access WILL get confused.

Could you explain the nature of these tables, and why you need two
fields rather than a single Date/Time field?

John W. Vinson[MVP]