simple update query

[tschaeffer]

I'm trying to perform a test update query that is representative of an
update query I'm going to want to run later this week, but can't get this to
seem to work. Any suggestions for this?

I have two tables: "Reports_byCountry" and "Reports_byLanguageDate" Each
has some of the data regarding reports published each month. I essentially
want to join them based on their unique ID's, but would like to do it by
updating the records in the latter table with the info in the former.


This table has the fields [Coverage] and [One_Liner]:

Reports_byCountry ReportID Coverage One_Liner
1000000 r2 Growing season has begun
1000001 bf Pasture and livestock conditions good
1000003 r2 An agronomic drought is underway



I would like for my update query to update similarly named fields in this
table based on the [ReportID] field:

Reports_byLanguageDate ReportID LANGUAGE DUE_DATE DATE_RECEIVED Coverage
One_Liner
1000000 en 6/15/2000 7/15/2000


1000001 en 7/15/2000 8/15/2000


1000003 en 8/15/2000 9/15/2000




There is a 1 - 1 relationship established between them based on their Report
ID, fyi

Here is the query I have written:

UPDATE Reports_byLanguageDate
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage
WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;


When I run it, it asks me for parameters for each of the fields in the
[Reports_byCountry] table, and regardless of what I put in for those
parameters, it updates 0 records. Not sure what I'm doing wrong. The
parameters I'm giving it ("*" or "is not null" or anything else) are
probably wrong, but can't find out what is correct. Any suggestions?

thanks - Toby

 

[tschaeffer]

reposting with tables correctly layed out.

Toby



----------------------------

I'm trying to perform a test update query that is representative of an
update query I'm going to want to run later this week, but can't get this to
seem to work. Any suggestions for this?

I have two tables: "Reports_byCountry" and "Reports_byLanguageDate" Each
has some of the data regarding reports published each month. I essentially
want to join them based on their unique ID's, but would like to do it by
updating the records in the latter table with the info in the former.

This table has the fields [Coverage] and [One_Liner]:



Reports_byCountry

ReportID Coverage One_Liner
1000000 r2 Growing season has begun
1000001 bf Pasture and livestock conditions good
1000003 r2 An agronomic drought is underway



I would like for my update query to update similarly named fields in this
table based on the [ReportID] field:

Reports_byLanguageDate

ReportID LANGUAGE DUE_DATE DATE_RECEIVED Coverage One_Liner
1000000 en 6/15/2000 7/15/2000
NULL NULL
1000001 en 7/15/2000 8/15/2000
NULL NULL
1000003 en 8/15/2000 9/15/2000
NULL NULL




There is a 1 - 1 relationship established between them based on their
Report ID, fyi

Here is the query I have written:

UPDATE Reports_byLanguageDate
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage
WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;


When I run it, it asks me for parameters for each of the fields in the
[Reports_byCountry] table, and regardless of what I put in for those
parameters, it updates 0 records. Not sure what I'm doing wrong. The
parameters I'm giving it ("*" or "is not null" or anything else) are
probably wrong, but can't find out what is correct. Any suggestions?

thanks - Toby

 

[Dale Fye]

Toby,

I think this is what you are looking for. You have to have both tables in
the query, and you didn't.

UPDATE Reports_byLanguageDate
INNER JOIN Reports_byCountry
ON Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage



HTH
Dale

 

[tschaeffer]

Access wouldn't let the Inner Join into the update query. A colleague
suggested essentially the same thing - putting the other table's name into
the UPDATE part of the expression (see below) - this worked the way I was
hoping it would.

Note that doing it like this makes it necessary to specify which table
you're updating in the SET part of the expression, since both source tables
have fields with the same name.

Thanks - Toby

UPDATE Reports_byLanguageDate, Reports_byCountry

SET Reports_byLanguageDate.Coverage = Reports_byCountry.Coverage,

Reports_byLanguageDate.One_Liner = Reports_byCountry.Coverage

WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;

Toby,

I think this is what you are looking for. You have to have both tables in
the query, and you didn't.

UPDATE Reports_byLanguageDate
INNER JOIN Reports_byCountry
ON Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage



HTH
Dale

I'm trying to perform a test update query that is representative of an
update query I'm going to want to run later this week, but can't get this
to seem to work. Any suggestions for this?

I have two tables: "Reports_byCountry" and "Reports_byLanguageDate"
Each has some of the data regarding reports published each month. I
essentially want to join them based on their unique ID's, but would like
to do it by updating the records in the latter table with the info in the
former.


This table has the fields [Coverage] and [One_Liner]:

Reports_byCountry ReportID Coverage One_Liner
1000000 r2 Growing season has begun
1000001 bf Pasture and livestock conditions good
1000003 r2 An agronomic drought is underway



I would like for my update query to update similarly named fields in this
table based on the [ReportID] field:

Reports_byLanguageDate ReportID LANGUAGE DUE_DATE DATE_RECEIVED Coverage
One_Liner
1000000 en 6/15/2000 7/15/2000


1000001 en 7/15/2000 8/15/2000


1000003 en 8/15/2000 9/15/2000




There is a 1 - 1 relationship established between them based on their
Report ID, fyi

Here is the query I have written:

UPDATE Reports_byLanguageDate
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage
WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;


When I run it, it asks me for parameters for each of the fields in the
[Reports_byCountry] table, and regardless of what I put in for those
parameters, it updates 0 records. Not sure what I'm doing wrong. The
parameters I'm giving it ("*" or "is not null" or anything else) are
probably wrong, but can't find out what is correct. Any suggestions?

thanks - Toby

 

[Dale Fye]

Toby,

When you say "Access wouldn't let the Inner Join" into the update query,
what do you mean. Do you mean that when you did this, you received a "not
updatable" error message. I created a query similar to this last night in a
database where the ReportID was the PK and I am absolutely sure I had a 1 to
1 relationship between the two tables.

If you got a "not updateable" error message, one of the causes is that you
are trying to update the 1 side of a one to many relationship, another is
that the fields in a JOIN are not indexed correctly (there is no primary key
or unique index on the JOINed fields)

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Access wouldn't let the Inner Join into the update query. A colleague
suggested essentially the same thing - putting the other table's name into
the UPDATE part of the expression (see below) - this worked the way I was
hoping it would.

Note that doing it like this makes it necessary to specify which table
you're updating in the SET part of the expression, since both source tables
have fields with the same name.

Thanks - Toby

UPDATE Reports_byLanguageDate, Reports_byCountry

SET Reports_byLanguageDate.Coverage = Reports_byCountry.Coverage,

Reports_byLanguageDate.One_Liner = Reports_byCountry.Coverage

WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;

Toby,

I think this is what you are looking for. You have to have both tables in
the query, and you didn't.

UPDATE Reports_byLanguageDate
INNER JOIN Reports_byCountry
ON Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage



HTH
Dale

I'm trying to perform a test update query that is representative of an
update query I'm going to want to run later this week, but can't get this
to seem to work. Any suggestions for this?

I have two tables: "Reports_byCountry" and "Reports_byLanguageDate"
Each has some of the data regarding reports published each month. I
essentially want to join them based on their unique ID's, but would like
to do it by updating the records in the latter table with the info in the
former.


This table has the fields [Coverage] and [One_Liner]:

Reports_byCountry ReportID Coverage One_Liner
1000000 r2 Growing season has begun
1000001 bf Pasture and livestock conditions good
1000003 r2 An agronomic drought is underway



I would like for my update query to update similarly named fields in this
table based on the [ReportID] field:

Reports_byLanguageDate ReportID LANGUAGE DUE_DATE DATE_RECEIVED Coverage
One_Liner
1000000 en 6/15/2000 7/15/2000


1000001 en 7/15/2000 8/15/2000


1000003 en 8/15/2000 9/15/2000




There is a 1 - 1 relationship established between them based on their
Report ID, fyi

Here is the query I have written:

UPDATE Reports_byLanguageDate
SET [Coverage] = Reports_byCountry.Coverage,
[One_Liner] = Reports_byCountry.Coverage
WHERE Reports_byLanguageDate.ReportID=Reports_byCountry.ReportID;


When I run it, it asks me for parameters for each of the fields in the
[Reports_byCountry] table, and regardless of what I put in for those
parameters, it updates 0 records. Not sure what I'm doing wrong. The
parameters I'm giving it ("*" or "is not null" or anything else) are
probably wrong, but can't find out what is correct. Any suggestions?

thanks - Toby