Update Query Error "Key Violations"

[Jim Dudley]

My query says that it is going to update 70 records. That is what it is
supposed to do. It says that it cannot update 63 of those records due to "Key
Violations".

What is a key violation?

My Table that is being updated to has a primary key and it is joined to the
corresponding [field] in the many Table, [field] is not a key field. It is
indexed allowing dups.

The two tables do not share a common Primary Key.

How do you correct this problem? I need all 70 records to post not just 7.

Thank you.

 

[KARL DEWEY]

You said 'Update' which means you are changing data in existing records. The
'Key Violation' would be that you are changing the 'Foreign Key' of the many
table to something that is not in the one table Primary Key field.
Or, changing a Primary Key field data and do not have Cascade Update
selected in the relationship.

 

[Jim Dudley]

You are correct, the Table being updated contains the Primary Key and the
Updating Table contains the foreign key.
Referential Integrity is not turned nor the cascade options.
When I try to create the Relationship and turn on Integrity, it tells me:
"Relationship must be on the same number of fields with the same data types."
I checked my Table structures and they do not contain the same fields but
the fields that are contained are of the same data types.
Any suggestions...
Thank you.
JD

 

[Jim Dudley]

You are correct. I joined the Primary Key in the One Table to the Foreign Key
in the Many Table in my Query but when I look at the Relationship grid for my
application their is no relationship defined.

I tried to Relate the Primary Key in the One Table to the Foreign Key in the
Many Table and set Integrity on. It will allow me to create the Relationship
but when I try to enable Integrity it tells me "Relationship must be on the
same number of fields with the same data type.

I checked my Tables design views and all the fields are not the same but the
fields involved in the query are of the same data types. i.e. number to
number or text to text. Can you only use integrity with tables that have
exactly the same fields and structure?

I look forward to your reply.

Thank you.

JD

You said 'Update' which means you are changing data in existing records. The
'Key Violation' would be that you are changing the 'Foreign Key' of the many
table to something that is not in the one table Primary Key field.
Or, changing a Primary Key field data and do not have Cascade Update
selected in the relationship.

My query says that it is going to update 70 records. That is what it is
supposed to do. It says that it cannot update 63 of those records due to "Key
Violations".

What is a key violation?

My Table that is being updated to has a primary key and it is joined to the
corresponding [field] in the many Table, [field] is not a key field. It is
indexed allowing dups.

The two tables do not share a common Primary Key.

How do you correct this problem? I need all 70 records to post not just 7.

Thank you.

 

[KARL DEWEY]

I thought you had a Perent and Child table situation. If you are updating
the One from the Many then you are doing something very wrong.

Maybe you can explain your process a little more. Why would you need to
update in this manner?

 

[Jim Dudley]

I have two Tables.
1. [Workshop_Registrations] which contains 3K + records. Each record
contains data on ONE student's attendance at a workshop. Each student earns
Credits toward 5 Levels of Certificates (Over 4 Years) for each different
type of workshop they attend. There are 5 Types of Workshops and many more
Topics within each type of Workshop. Each student when they register must use
their Student Number which is issued by the University and is Unique to them.
So in summary this table contains records of attendance at each event and
each student may have several records in the table. (One for each workshop).
Each Worshop has a unique code as well.

2. [Workshop_Credits] - Consist of one record for each student, no
duplicates are allowed. The Student Number is the Primary Key.
Each record is to record the credits earned, catagorized by one of the 5
general types of Workshop. There is a field for each type of Workshop and the
credits for each student are accumulated in these five fields. These five
fields are then totaled to give the total number of credits for all types of
Workshops that an individual student has earned. These Totals are then
evaluated to determine which level of Certificate has been earned. There are
5 Levels of certificates available. They are awarded on a cumultive basis for
total Credits. e.g. 9 Total Credits=Level 1, 17 Total Credits=Level 2, etc.
We need this file to be able to summarize the activities recorded in the
[Workshop_Registrations] Table.

Does this make sense to you now?

I am a novus at this so all constuctive criticsm is welcome.

JD

 

[John W. Vinson]

You are correct. I joined the Primary Key in the One Table to the Foreign Key
in the Many Table in my Query but when I look at the Relationship grid for my
application their is no relationship defined.

I tried to Relate the Primary Key in the One Table to the Foreign Key in the
Many Table and set Integrity on. It will allow me to create the Relationship
but when I try to enable Integrity it tells me "Relationship must be on the
same number of fields with the same data type.

I checked my Tables design views and all the fields are not the same but the
fields involved in the query are of the same data types. i.e. number to
number or text to text. Can you only use integrity with tables that have
exactly the same fields and structure?

My guess is that you're yet another victim of the "Lookup Fields" misfeature.
Are you trying to establish a relationship *on a Lookup field*? If so, be
aware that the lookup field does not contain what it appears to contain. What
are the datatypes of the relevant fields in your tables?

 

[Jim Dudley]

John,

I checked, neither table has any lookup fields. The fields that I am dealing
with match in data types, i.e. text to text, number to number. The Many table
contains a foreign key from the one table. Some time ago I could get this to
work if I used a limited scope on the many side. Post the results of one
Workshop at a time. But my feeling is that I should be able to post all. When
it has finished posting a record for one student on the many side, it moves
on to the next record and starts the update process over until it has posted
all of the many records to the one table. This would mean it may find 10-11
records on the many side to post to one record on the one side (My summary
Table).

Any further insight would be appreciated.

 

[Jim Dudley]

John,

I checked, neither table has any lookup fields. The fields that I am dealing
with match in data types, i.e. text to text, number to number. The Many table
contains a foreign key from the one table. Some time ago I could get this to
work if I used a limited scope on the many side. Post the results of one
Workshop at a time. But my feeling is that I should be able to post all. When
it has finished posting a record for one student on the many side, it moves
on to the next record and starts the update process over until it has posted
all of the many records to the one table. This would mean it may find 10-11
records on the many side to post to one record on the one side (My summary
Table).

Any further insight would be appreciated.

 

[John W. Vinson]

John,

I checked, neither table has any lookup fields. The fields that I am dealing
with match in data types, i.e. text to text, number to number. The Many table
contains a foreign key from the one table. Some time ago I could get this to
work if I used a limited scope on the many side. Post the results of one
Workshop at a time. But my feeling is that I should be able to post all. When
it has finished posting a record for one student on the many side, it moves
on to the next record and starts the update process over until it has posted
all of the many records to the one table. This would mean it may find 10-11
records on the many side to post to one record on the one side (My summary
Table).

Any further insight would be appreciated.

Please open your query in SQL view and post it here; indicate what field
you're trying to update and what you want it updated to.

 

[Jim Dudley]

I had been trying different approaches to this posting problem this was
approach 1:

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz([Workshop_Credits].[EL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time()
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]));

This was approach 2:

UPDATE WS_REGSX1Type LEFT JOIN Workshop_Credits ON WS_REGSX1Type.Student_Num
= Workshop_Credits.Student_Num SET Workshop_Credits.EL_Credits =
Val(NZ([Workshop_Credits].[EL_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0)),
Workshop_Credits.Total_Credits =
Val(NZ([Workshop_Credits].[Total_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0));

Neither one worked.

I appreciate your interest.

JD

 

[John W. Vinson]

I had been trying different approaches to this posting problem this was
approach 1:

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz([Workshop_Credits].[EL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time()
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]));

This was approach 2:

UPDATE WS_REGSX1Type LEFT JOIN Workshop_Credits ON WS_REGSX1Type.Student_Num
= Workshop_Credits.Student_Num SET Workshop_Credits.EL_Credits =
Val(NZ([Workshop_Credits].[EL_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0)),
Workshop_Credits.Total_Credits =
Val(NZ([Workshop_Credits].[Total_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0));

Neither one worked.

Again:

You can see your database. You understand your structure and your logic. We
don't.

What's in each table? How are the tables related? What do you want to
accomplish? Are you perhaps trying to STORE the sum of the credits in the many
side table into a field in the one side table? If so, don't: here's my speil
on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.

 

[Jim Dudley]

John, I will try your suggestion puting an expression into my form. Can I
write an expression that will sum all the entries for one student for a
certain type when the records exist in another table. Or can I have a form
that runs a query first and then calculates the result from the query result?
I agree with your theory but do not know how to achieve my goal.

IN the Many table students have several records, one for each workshop they
have attended. Each workshop earns credit(s) for one of 5 types of workshops.
The many record also contains the workshop type and the credit(s) earned. I
need a form that summarizes this data for each student and the summary data
is used in other calculation to determine what cerificates the student has
earned. Certificates are earned by completing a given number of credits in
each of the 5 types or workshops.

This needs to come up quickly in a form so that enquiries regarding student
status, either by phone or email can be answered.

Thank you for your continued effort to understand what I am trying to
accomplish.

JD

I had been trying different approaches to this posting problem this was
approach 1:

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz([Workshop_Credits].[EL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time()
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]));

This was approach 2:

UPDATE WS_REGSX1Type LEFT JOIN Workshop_Credits ON WS_REGSX1Type.Student_Num
= Workshop_Credits.Student_Num SET Workshop_Credits.EL_Credits =
Val(NZ([Workshop_Credits].[EL_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0)),
Workshop_Credits.Total_Credits =
Val(NZ([Workshop_Credits].[Total_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0));

Neither one worked.

Again:

You can see your database. You understand your structure and your logic. We
don't.

What's in each table? How are the tables related? What do you want to
accomplish? Are you perhaps trying to STORE the sum of the credits in the many
side table into a field in the one side table? If so, don't: here's my speil
on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.

 

[John Spencer]

Let's see in the first Set you have quotes around the table and field
names and you have misspelled a table name in the IIF statement.

(Val(nz("Workshop_Credits].[EL_Credits]",0)) << should be no quotes

IIf(([Worshop_Registrations].[Type]="EL") << should be
Workshop_Registrations no Worshop_Registrations

You also have an abundance of parentheses. Not all of them are needed,
but they should not cause a problem. I'll get you started and allow you
to clean up the mess.

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits
ON Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num

SET Workshop_Credits.EL_Credits =
IIf([Workshop_Registrations].[Type]="EL"
, Val(nz(Workshop_Credits].[EL_Credits],0)) +
Val(nz([Workshop_Registrations].[Credits],0)))

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

I had been trying different approaches to this posting problem this was
approach 1:

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz([Workshop_Credits].[EL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time()
WHERE (((Workshop_Registrations.Workshop_Code)=[Workshop Code]));

This was approach 2:

UPDATE WS_REGSX1Type LEFT JOIN Workshop_Credits ON WS_REGSX1Type.Student_Num
= Workshop_Credits.Student_Num SET Workshop_Credits.EL_Credits =
Val(NZ([Workshop_Credits].[EL_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0)),
Workshop_Credits.Total_Credits =
Val(NZ([Workshop_Credits].[Total_Credits],0))+Val(NZ([WS_REGSX1Type]![Credits],0));

Neither one worked.

Again:

You can see your database. You understand your structure and your logic. We
don't.

What's in each table? How are the tables related? What do you want to
accomplish? Are you perhaps trying to STORE the sum of the credits in the many
side table into a field in the one side table? If so, don't: here's my speil
on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.

 

[Bob Barrows]

John, I will try your suggestion puting an expression into my form.
Can I
write an expression that will sum all the entries for one student for
a
certain type when the records exist in another table. .

Yes you can - see the Domain functions (DSum, etc.)

Or can I have a
form
that runs a query first and then calculates the result from the query
result?

Hard to say without knowing your database but, if worse comes to worst, you
can have some VBA code run in the Form's onload event that does this.