Update Query Error 2007 Correction

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.
 
J

John Spencer

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
'====================================================
 
J

Jim Dudley

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...
 
J

John Spencer

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
'====================================================
 
J

Jim Dudley

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...
 
J

Jim Dudley

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...
 
J

John Spencer

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
'====================================================
 
J

Jim Dudley

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...
 
J

Jim Dudley

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...
 
J

John Spencer

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query, 2007 not working?? 0
Update Query, 2007 - Not Working?? 1
Update Query, 2007 - Not Working? 6
Query (UPDATE) Error 0
update query - key violation error 2
Update Query Error "Key Violations" 14
'Operation must use an updateable query' 1
Access update query 2

Top