Append Query Key Violation

  • Thread starter Thread starter shep
  • Start date Start date
S

shep

MS Office 2003
I am not a programmer

I want to append record from tblContactsV1 to tblPatientV1. tblContactsV1
has autonumber primary key; tblPatientV1 has a single primary key
(Chartnumber). I assume this is why record does not append "key violation."
If that is cause of key violation, how can I best correct?

Thanks
 
Show us the structure of both tables -- field names, data types, and which
ones are primary key in each. Is there a foreign key in tblPatientV1 that is
related back to the primary key in tblContactsV1?
 
Ken Snell (MVP) said:
Show us the structure of both tables -- field names, data types, and which
ones are primary key in each. Is there a foreign key in tblPatientV1 that is
related back to the primary key in tblContactsV1?
--

Ken Snell
<MS ACCESS MVP>






Here is documentation for the two tables. Thanks
Click to expand...

Table: Contacts Page: 1
Properties
DateCreated: 12/9/2005 3:35:15 PM DefaultView: Datasheet
GUID: {guid {77FB31CC-CAD0- LastUpdated: 1/6/2006 1:27:26 PM
450A-9998-
NameMap: Long binary data OrderByOn: False
Orientation: Left-to-Right RecordCount: 3848
Updatable: True
Columns
Name Type Size
ContactID (Primary Key) Long Integer 4
FirstName Text 50
LastName Text 50
MiddleInitial Text 50
Address1 Text 255
Address2 Text 50
City Text 50
State Text 20
Zip Text 20
Company Text 50
Title Text 50
HomeTel Text 50
WorkTel Text 30
WorkExt Text 20
CellPhone Text 30
Fax Text 30
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
ContactTypeID Long Integer 4
ContactSource Text 50
Notes Memo -
DateEntered Date/Time 8
ChartNumber Text 50
Position Text 50

Table: tblPatientV1 Page: 1
Properties
DateCreated: 7/3/2005 11:30:49 AM DefaultView: Datasheet
GUID: {guid {5E5C8815-CA73- LastUpdated: 1/19/2006 9:11:30 AM
41F5-BB97-
OrderByOn: False Orientation: Left-to-Right
RecordCount: 52 SubdatasheetName: [None]
Updatable: True
Columns
Name Type Size
LastName Text 50
FirstName Text 50
MiddleInitial Text 50
Gender Long Integer 4
DOB Date/Time 8
SSSN Text 50
ChartNumber (Primary Key) Text 50
Address1 Text 50
Address2 Text 50
City Text 50
State Text 50
Zip Text 50
County Text 50
Language Long Integer 4
HomeTel Text 50
WorkTel Text 50
CellPhone Text 50
Physician Text 50
ClinicName Text 50
DischargeDate Date/Time 8
Race Long Integer 4
Initial Visit Date/Time 8
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
 
How do the two tables relate to each other? I don't see any obvious fields
in the two tables that would appear to give you this relationship?

Can you also show us the SQL statement of the append query that you're
trying to use -- the one that gives you the error?

--

Ken Snell
<MS ACCESS MVP>



shep said:
Ken Snell (MVP) said:
Show us the structure of both tables -- field names, data types, and
which
ones are primary key in each. Is there a foreign key in tblPatientV1 that
is
related back to the primary key in tblContactsV1?
--

Ken Snell
<MS ACCESS MVP>






Here is documentation for the two tables. Thanks
Click to expand...

Table: Contacts Page: 1
Properties
DateCreated: 12/9/2005 3:35:15 PM DefaultView: Datasheet
GUID: {guid {77FB31CC-CAD0- LastUpdated: 1/6/2006 1:27:26 PM
450A-9998-
NameMap: Long binary data OrderByOn: False
Orientation: Left-to-Right RecordCount: 3848
Updatable: True
Columns
Name Type Size
ContactID (Primary Key) Long Integer 4
FirstName Text 50
LastName Text 50
MiddleInitial Text 50
Address1 Text 255
Address2 Text 50
City Text 50
State Text 20
Zip Text 20
Company Text 50
Title Text 50
HomeTel Text 50
WorkTel Text 30
WorkExt Text 20
CellPhone Text 30
Fax Text 30
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
ContactTypeID Long Integer 4
ContactSource Text 50
Notes Memo -
DateEntered Date/Time 8
ChartNumber Text 50
Position Text 50

Table: tblPatientV1 Page: 1
Properties
DateCreated: 7/3/2005 11:30:49 AM DefaultView: Datasheet
GUID: {guid {5E5C8815-CA73- LastUpdated: 1/19/2006 9:11:30 AM
41F5-BB97-
OrderByOn: False Orientation: Left-to-Right
RecordCount: 52 SubdatasheetName: [None]
Updatable: True
Columns
Name Type Size
LastName Text 50
FirstName Text 50
MiddleInitial Text 50
Gender Long Integer 4
DOB Date/Time 8
SSSN Text 50
ChartNumber (Primary Key) Text 50
Address1 Text 50
Address2 Text 50
City Text 50
State Text 50
Zip Text 50
County Text 50
Language Long Integer 4
HomeTel Text 50
WorkTel Text 50
CellPhone Text 50
Physician Text 50
ClinicName Text 50
DischargeDate Date/Time 8
Race Long Integer 4
Initial Visit Date/Time 8
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
Click to expand...
 
Ken
I think that the basic problem is that they do not relate. We have to
reassess, so I'm counseling this thread. As always, thanks for your help.

Ken Snell (MVP) said:
How do the two tables relate to each other? I don't see any obvious fields
in the two tables that would appear to give you this relationship?

Can you also show us the SQL statement of the append query that you're
trying to use -- the one that gives you the error?

--

Ken Snell
<MS ACCESS MVP>



shep said:
Ken Snell (MVP) said:
Show us the structure of both tables -- field names, data types, and
which
ones are primary key in each. Is there a foreign key in tblPatientV1 that
is
related back to the primary key in tblContactsV1?
--

Ken Snell
<MS ACCESS MVP>



MS Office 2003
I am not a programmer

I want to append record from tblContactsV1 to tblPatientV1.
tblContactsV1
has autonumber primary key; tblPatientV1 has a single primary key
(Chartnumber). I assume this is why record does not append "key
violation."
If that is cause of key violation, how can I best correct?

Thanks


Here is documentation for the two tables. Thanks
Click to expand...

Table: Contacts Page: 1
Properties
DateCreated: 12/9/2005 3:35:15 PM DefaultView: Datasheet
GUID: {guid {77FB31CC-CAD0- LastUpdated: 1/6/2006 1:27:26 PM
450A-9998-
NameMap: Long binary data OrderByOn: False
Orientation: Left-to-Right RecordCount: 3848
Updatable: True
Columns
Name Type Size
ContactID (Primary Key) Long Integer 4
FirstName Text 50
LastName Text 50
MiddleInitial Text 50
Address1 Text 255
Address2 Text 50
City Text 50
State Text 20
Zip Text 20
Company Text 50
Title Text 50
HomeTel Text 50
WorkTel Text 30
WorkExt Text 20
CellPhone Text 30
Fax Text 30
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
ContactTypeID Long Integer 4
ContactSource Text 50
Notes Memo -
DateEntered Date/Time 8
ChartNumber Text 50
Position Text 50

Table: tblPatientV1 Page: 1
Properties
DateCreated: 7/3/2005 11:30:49 AM DefaultView: Datasheet
GUID: {guid {5E5C8815-CA73- LastUpdated: 1/19/2006 9:11:30 AM
41F5-BB97-
OrderByOn: False Orientation: Left-to-Right
RecordCount: 52 SubdatasheetName: [None]
Updatable: True
Columns
Name Type Size
LastName Text 50
FirstName Text 50
MiddleInitial Text 50
Gender Long Integer 4
DOB Date/Time 8
SSSN Text 50
ChartNumber (Primary Key) Text 50
Address1 Text 50
Address2 Text 50
City Text 50
State Text 50
Zip Text 50
County Text 50
Language Long Integer 4
HomeTel Text 50
WorkTel Text 50
CellPhone Text 50
Physician Text 50
ClinicName Text 50
DischargeDate Date/Time 8
Race Long Integer 4
Initial Visit Date/Time 8
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
Click to expand...
Click to expand...
 
Ken Snell (MVP) said:
How do the two tables relate to each other? I don't see any obvious fields
in the two tables that would appear to give you this relationship?

Can you also show us the SQL statement of the append query that you're
trying to use -- the one that gives you the error?

--

Ken Snell
<MS ACCESS MVP>



shep said:
Ken Snell (MVP) said:
Show us the structure of both tables -- field names, data types, and
which
ones are primary key in each. Is there a foreign key in tblPatientV1 that
is
related back to the primary key in tblContactsV1?
--

Ken Snell
<MS ACCESS MVP>



MS Office 2003
I am not a programmer

I want to append record from tblContactsV1 to tblPatientV1.
tblContactsV1
has autonumber primary key; tblPatientV1 has a single primary key
(Chartnumber). I assume this is why record does not append "key
violation."
If that is cause of key violation, how can I best correct?

Thanks


Here is documentation for the two tables. Thanks
Click to expand...

Table: Contacts Page: 1
Properties
DateCreated: 12/9/2005 3:35:15 PM DefaultView: Datasheet
GUID: {guid {77FB31CC-CAD0- LastUpdated: 1/6/2006 1:27:26 PM
450A-9998-
NameMap: Long binary data OrderByOn: False
Orientation: Left-to-Right RecordCount: 3848
Updatable: True
Columns
Name Type Size
ContactID (Primary Key) Long Integer 4
FirstName Text 50
LastName Text 50
MiddleInitial Text 50
Address1 Text 255
Address2 Text 50
City Text 50
State Text 20
Zip Text 20
Company Text 50
Title Text 50
HomeTel Text 50
WorkTel Text 30
WorkExt Text 20
CellPhone Text 30
Fax Text 30
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
ContactTypeID Long Integer 4
ContactSource Text 50
Notes Memo -
DateEntered Date/Time 8
ChartNumber Text 50
Position Text 50

Table: tblPatientV1 Page: 1
Properties
DateCreated: 7/3/2005 11:30:49 AM DefaultView: Datasheet
GUID: {guid {5E5C8815-CA73- LastUpdated: 1/19/2006 9:11:30 AM
41F5-BB97-
OrderByOn: False Orientation: Left-to-Right
RecordCount: 52 SubdatasheetName: [None]
Updatable: True
Columns
Name Type Size
LastName Text 50
FirstName Text 50
MiddleInitial Text 50
Gender Long Integer 4
DOB Date/Time 8
SSSN Text 50
ChartNumber (Primary Key) Text 50
Address1 Text 50
Address2 Text 50
City Text 50
State Text 50
Zip Text 50
County Text 50
Language Long Integer 4
HomeTel Text 50
WorkTel Text 50
CellPhone Text 50
Physician Text 50
ClinicName Text 50
DischargeDate Date/Time 8
Race Long Integer 4
Initial Visit Date/Time 8
Email1 Text 50
Email2 Text 50
Email3 Text 50
Email4 Text 50
Pager Text 50
Click to expand...
Click to expand...
 
Back
Top