Update Query Error 2007 Correction

  • Thread starter Thread starter Jim Dudley
  • Start date Start date
J

Jim Dudley

Sorry for the first 2 incomplete questions.

Here is the SQL code I am trying to use.

Any Help will be appreciated.

Query Error Update WS_Cr from WS_Regs

Query:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] RIGHT JOIN Workshop_Credits
ON [QryWS_Reg_Select Recs_Updte_WS_Credits].Student_Num =
Workshop_Credits.Student_Num
SET Workshop_Credits.PD_Credits = [PD_Credits]+1
WHERE ((([QryWS_Reg_Select Recs_Updte_WS_Credits].Workshop_Code)=[Workshop
Code]));

Error:

Depending on Join Type, either Updates 1 record only or fails to update 19
of 20 records. (Key Violations)

Note:

[Student_Num] is key field in Workshop_Credits Table and is indexed allowing
duplicates in the Worshop_Registrations Table.

I believe WS_Credits is the one Table and WS_Registrations is the many table.
 
First of all I would SPECIFY the table/query names for all the fields.
Especially when you have field in each table with the same name.

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q
RIGHT JOIN Workshop_Credits AS W
ON [Q].Student_Num = W.Student_Num

SET W.PD_Credits = [Q].[PD_Credits]+1
WHERE [Q].Workshop_Code=W.[Workshop Code]

One question is the field in Workshop_Credits named WorkShop Code or
Workshop_Code?

Why are you using a right join? Normally, I would expect to use an
inner join like the following.

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q
INNER JOIN Workshop_Credits AS W
ON [Q].Student_Num = W.Student_Num
AND [Q].Workshop_Code = W.[Workshop Code]
SET W.PD_Credits = [Q].[PD_Credits]+1

If you intent is to increase Workshop_Code.PD_Credits by 1 then you need
to change
SET W.PD_Credits = [Q].[PD_Credits]+1
to
SET W.PD_Credits = [W].[PD_Credits]+1

And if the value of PD_Credits is null then you may need to handle that with
SET W.PD_Credits = Val(Nz([Q].[PD_Credits],0))+1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thank you John,

The two proper table names are:
[Workshop_Registrations] The table containing the information to be posted.
[Workshop_Credits] The table to be updated.

The Query is a selection query made from the table [Workshop_Registrations]
(contains data on registrants and credits earned for all workshops) The query
selects those registrants who attended a specific workshop (each workshop has
a unique code), their student number [Student_Num] and the value of the
credit they earned.

The [Workshop_Credit] table will recieve the update in
[Workshop_Credit].[PD_Credits] (existing value + (in this case) 1.
To record that the [Workshop_Registrations] credit was posted, we change the
[Workshops_Registrants].[Posted] field to "yes" from "no" and put the posting
date into [Workshops_Registrants].[Posted_Date] Date()

I am sorry, but I do not know the difference between and inner and outer Join.
But I do appreciate the heads up on the recieving field in
[Workshop-Credits] having a null or zero value. I had not thought of that as
of yet.

I will work with your suggestions and see how it works. I was using the
query builder to create the query but posted the SQL version for experts like
you to maybe understand what I am trying to do.

Any simple explanation on the difference between innner and outer joins
would be appreciated.

Thanks again....

JD
--
Thanks in advance...

Jim


John Spencer said:
First of all I would SPECIFY the table/query names for all the fields.
Especially when you have field in each table with the same name.

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q
RIGHT JOIN Workshop_Credits AS W
ON [Q].Student_Num = W.Student_Num

SET W.PD_Credits = [Q].[PD_Credits]+1
WHERE [Q].Workshop_Code=W.[Workshop Code]

One question is the field in Workshop_Credits named WorkShop Code or
Workshop_Code?

Why are you using a right join? Normally, I would expect to use an
inner join like the following.

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q
INNER JOIN Workshop_Credits AS W
ON [Q].Student_Num = W.Student_Num
AND [Q].Workshop_Code = W.[Workshop Code]
SET W.PD_Credits = [Q].[PD_Credits]+1

If you intent is to increase Workshop_Code.PD_Credits by 1 then you need
to change
SET W.PD_Credits = [Q].[PD_Credits]+1
to
SET W.PD_Credits = [W].[PD_Credits]+1

And if the value of PD_Credits is null then you may need to handle that with
SET W.PD_Credits = Val(Nz([Q].[PD_Credits],0))+1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
Sorry for the first 2 incomplete questions.

Here is the SQL code I am trying to use.

Any Help will be appreciated.

Query Error Update WS_Cr from WS_Regs

Query:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] RIGHT JOIN Workshop_Credits
ON [QryWS_Reg_Select Recs_Updte_WS_Credits].Student_Num =
Workshop_Credits.Student_Num
SET Workshop_Credits.PD_Credits = [PD_Credits]+1
WHERE ((([QryWS_Reg_Select Recs_Updte_WS_Credits].Workshop_Code)=[Workshop
Code]));

Error:

Depending on Join Type, either Updates 1 record only or fails to update 19
of 20 records. (Key Violations)

Note:

[Student_Num] is key field in Workshop_Credits Table and is indexed allowing
duplicates in the Worshop_Registrations Table.

I believe WS_Credits is the one Table and WS_Registrations is the many table.
Click to expand...
Click to expand...
 
An INNER JOIN returns a row if there are records on BOTH side of the
join. That is with two tables (A and B) matched on a field named C in
both tables, there must be a value (for example 222) in field C of both
tables in order for a row to be returned.

A LEFT JOIN or a RIGHT JOIN will return a row FROM the Table on the Left
or Right side of the join even if there is not matching record in the
second table. If there is a matching record it will return the
information from the matching record and if there is no matching record
the columns will be blank (null).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thank you John, working on it now....

One more question if you have time.

If the table I am updating can find no matching record, in other words a new
student, what do I need to do to automatically create a new record for this
student in the Receiving table?

[Workshop_Registrations] updating student infromation to
[Workshop_Credits].[Field_Name] with [Student_Num] being the Primary Key in
[Workshop_Credits]

Thanks in advance...

Jim


John Spencer said:
An INNER JOIN returns a row if there are records on BOTH side of the
join. That is with two tables (A and B) matched on a field named C in
both tables, there must be a value (for example 222) in field C of both
tables in order for a row to be returned.

A LEFT JOIN or a RIGHT JOIN will return a row FROM the Table on the Left
or Right side of the join even if there is not matching record in the
second table. If there is a matching record it will return the
information from the matching record and if there is no matching record
the columns will be blank (null).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
Thank you John,

The two proper table names are:
[Workshop_Registrations] The table containing the information to be posted.
[Workshop_Credits] The table to be updated.

The Query is a selection query made from the table [Workshop_Registrations]
(contains data on registrants and credits earned for all workshops) The query
selects those registrants who attended a specific workshop (each workshop has
a unique code), their student number [Student_Num] and the value of the
credit they earned.

The [Workshop_Credit] table will recieve the update in
[Workshop_Credit].[PD_Credits] (existing value + (in this case) 1.
To record that the [Workshop_Registrations] credit was posted, we change the
[Workshops_Registrants].[Posted] field to "yes" from "no" and put the posting
date into [Workshops_Registrants].[Posted_Date] Date()

I am sorry, but I do not know the difference between and inner and outer Join.
But I do appreciate the heads up on the recieving field in
[Workshop-Credits] having a null or zero value. I had not thought of that as
of yet.

I will work with your suggestions and see how it works. I was using the
query builder to create the query but posted the SQL version for experts like
you to maybe understand what I am trying to do.

Any simple explanation on the difference between innner and outer joins
would be appreciated.

Thanks again....

JD
Click to expand...
Click to expand...
 
John, the query seemed to work to a point. It updated one record not the
whole 20 that were selected. I suspect that the sample data I am working with
does not have matching records in the [Workshop_Credits] table. How do I get
the query to tell me how many records it updated and to add a new record if
no matching [Student_Num] is found in the table that is being updated
[WORKSHOP_CREDITS]?

This is the SQL code I used:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES". SET Q.DATE_POSTED = DATE();

THIS IS THE SQL CODE WHEN I RE-OPENED THE QUERY TO RE-EXAMINE IT.

SELECT Workshop_Registrations.Workshop_Code, Workshop_Registrations.Type,
Workshop_Registrations.Credits, Workshop_Registrations.Workshop_Date,
Workshop_Registrations.Student_Num, Workshop_Registrations.Last_Name,
Workshop_Registrations.Posted, Workshop_Registrations.Date_Posted,
Workshop_Registrations.ID
FROM Workshop_Registrations
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]))
ORDER BY Workshop_Registrations.Workshop_Code,
Workshop_Registrations.Student_Num;

WHY THE CHANGE?


--
Thanks in advance...

Jim


John Spencer said:
An INNER JOIN returns a row if there are records on BOTH side of the
join. That is with two tables (A and B) matched on a field named C in
both tables, there must be a value (for example 222) in field C of both
tables in order for a row to be returned.

A LEFT JOIN or a RIGHT JOIN will return a row FROM the Table on the Left
or Right side of the join even if there is not matching record in the
second table. If there is a matching record it will return the
information from the matching record and if there is no matching record
the columns will be blank (null).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
Thank you John,

The two proper table names are:
[Workshop_Registrations] The table containing the information to be posted.
[Workshop_Credits] The table to be updated.

The Query is a selection query made from the table [Workshop_Registrations]
(contains data on registrants and credits earned for all workshops) The query
selects those registrants who attended a specific workshop (each workshop has
a unique code), their student number [Student_Num] and the value of the
credit they earned.

The [Workshop_Credit] table will recieve the update in
[Workshop_Credit].[PD_Credits] (existing value + (in this case) 1.
To record that the [Workshop_Registrations] credit was posted, we change the
[Workshops_Registrants].[Posted] field to "yes" from "no" and put the posting
date into [Workshops_Registrants].[Posted_Date] Date()

I am sorry, but I do not know the difference between and inner and outer Join.
But I do appreciate the heads up on the recieving field in
[Workshop-Credits] having a null or zero value. I had not thought of that as
of yet.

I will work with your suggestions and see how it works. I was using the
query builder to create the query but posted the SQL version for experts like
you to maybe understand what I am trying to do.

Any simple explanation on the difference between innner and outer joins
would be appreciated.

Thanks again....

JD
Click to expand...
Click to expand...
 
That makes no sense at all. An update query cannot change to a select
query. I am stumped.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John, I apologize, it did not change, I re-opened another query by mistake.
The other SQL statement I have corrected and is giving me this error.

Error:
Circular reference caused by “QryWS_Reg_SelectRecs_Updte_WS_Credits"

SQL Stmt:
UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES", SET Q.DATE_POSTED = DATE();


Thanks in advance...

Jim


John Spencer said:
That makes no sense at all. An update query cannot change to a select
query. I am stumped.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
John, the query seemed to work to a point. It updated one record not the
whole 20 that were selected. I suspect that the sample data I am working with
does not have matching records in the [Workshop_Credits] table. How do I get
the query to tell me how many records it updated and to add a new record if
no matching [Student_Num] is found in the table that is being updated
[WORKSHOP_CREDITS]?

This is the SQL code I used:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES". SET Q.DATE_POSTED = DATE();

THIS IS THE SQL CODE WHEN I RE-OPENED THE QUERY TO RE-EXAMINE IT.

SELECT Workshop_Registrations.Workshop_Code, Workshop_Registrations.Type,
Workshop_Registrations.Credits, Workshop_Registrations.Workshop_Date,
Workshop_Registrations.Student_Num, Workshop_Registrations.Last_Name,
Workshop_Registrations.Posted, Workshop_Registrations.Date_Posted,
Workshop_Registrations.ID
FROM Workshop_Registrations
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]))
ORDER BY Workshop_Registrations.Workshop_Code,
Workshop_Registrations.Student_Num;

WHY THE CHANGE?
Click to expand...
Click to expand...
 
Thank you for your help, I did some research, slightly modified my table
structure and eliminated duplicate records in several places in the test
data and records missing student numbers. I spent a few hours working on the
queries and now have the tables updating. They are also posting dates on both
sides for the updates. I am pleased.

I do feel I will run into a problem when student that has never attended a
workshop, signs up and earns credits.

What happens when an update query cannot find a record to update to? Does it
crash or keep on running without updating the one record?

Look forward to your response.
--

Thanks in advance...

Jim


John Spencer said:
That makes no sense at all. An update query cannot change to a select
query. I am stumped.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
John, the query seemed to work to a point. It updated one record not the
whole 20 that were selected. I suspect that the sample data I am working with
does not have matching records in the [Workshop_Credits] table. How do I get
the query to tell me how many records it updated and to add a new record if
no matching [Student_Num] is found in the table that is being updated
[WORKSHOP_CREDITS]?

This is the SQL code I used:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES". SET Q.DATE_POSTED = DATE();

THIS IS THE SQL CODE WHEN I RE-OPENED THE QUERY TO RE-EXAMINE IT.

SELECT Workshop_Registrations.Workshop_Code, Workshop_Registrations.Type,
Workshop_Registrations.Credits, Workshop_Registrations.Workshop_Date,
Workshop_Registrations.Student_Num, Workshop_Registrations.Last_Name,
Workshop_Registrations.Posted, Workshop_Registrations.Date_Posted,
Workshop_Registrations.ID
FROM Workshop_Registrations
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]))
ORDER BY Workshop_Registrations.Workshop_Code,
Workshop_Registrations.Student_Num;

WHY THE CHANGE?
Click to expand...
Click to expand...
 
ONLY one SET per update query
Try the following

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W
ON (Q.Student_Num=W.Student_Num)
AND (Q.Workshop_Code=W.Workshop_Code)
SET W.PD_Credits = Val(Nz([Q].[Credits],0))+1
, Q.POSTED = "YES"
, Q.DATE_POSTED = DATE();

If Posted is a yes/no field and not a string field then change that line to:
Q.Posted=True

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
John, I apologize, it did not change, I re-opened another query by mistake.
The other SQL statement I have corrected and is giving me this error.

Error:
Circular reference caused by “QryWS_Reg_SelectRecs_Updte_WS_Credits"

SQL Stmt:
UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES", SET Q.DATE_POSTED = DATE();


Thanks in advance...

Jim


John Spencer said:
That makes no sense at all. An update query cannot change to a select
query. I am stumped.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
John, the query seemed to work to a point. It updated one record not the
whole 20 that were selected. I suspect that the sample data I am working with
does not have matching records in the [Workshop_Credits] table. How do I get
the query to tell me how many records it updated and to add a new record if
no matching [Student_Num] is found in the table that is being updated
[WORKSHOP_CREDITS]?

This is the SQL code I used:

UPDATE [QryWS_Reg_Select Recs_Updte_WS_Credits] AS Q INNER JOIN
Workshop_Credits AS W ON (Q.Student_Num=W.Student_Num) AND
(Q.Workshop_Code=W.Workshop_Code) SET W.PD_Credits =
Val(Nz([Q].[Credits],0))+1, SET Q.POSTED = "YES". SET Q.DATE_POSTED = DATE();

THIS IS THE SQL CODE WHEN I RE-OPENED THE QUERY TO RE-EXAMINE IT.

SELECT Workshop_Registrations.Workshop_Code, Workshop_Registrations.Type,
Workshop_Registrations.Credits, Workshop_Registrations.Workshop_Date,
Workshop_Registrations.Student_Num, Workshop_Registrations.Last_Name,
Workshop_Registrations.Posted, Workshop_Registrations.Date_Posted,
Workshop_Registrations.ID
FROM Workshop_Registrations
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]))
ORDER BY Workshop_Registrations.Workshop_Code,
Workshop_Registrations.Student_Num;

WHY THE CHANGE?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top