Re: Key Violations in Append Queries

The numbers are the same in each filed as the rest in the tables. That is why
it is so strange. I even added a test field and that does not append. It now
returns with the other 3 records and cant be removed from the query results.
Some records append and some dont.

"Tom Ellison" wrote:

Dear Troy:

Are the rows with the same ID values duplicates in all respects, or just in
the ID value?

The decision to be made is whether these are new rows. If so, they would
need new ID values, and the related rows would need the matching new ID
values as well. This presumes that such ID values are hidden from users.
If so, then you need only be sure to change them consistently and things
should function properly.

Tom Ellison


"troy" <troy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D564D5B-F27A-4E0D-921E-A590ADECB1D4@xxxxxxxxxxxxxxxx
Yes the form ID is a number data type and it is joined to another table
with
the same data type. Both carry the same data. One is linked and the other
is
on SQL. I have no clue why it is this way I was the luck one to get to fix
it. Pretty hard when you have all this garboage to filter though. Just
seems
like those same 3 records stay thoughout this append query and the other
update and append queries I run following the history one. Any ideas?

"Tom Ellison" wrote:

Dear Troy:

Normally, if a primary key or other unique index is created, it is
because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I
do
not know. But this designed feature is working as it should (again
assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column?
Are
there one or more other tables that are related on this column, and if
so,
what are the relationships?

It may be that you should just omit this from the append query, and allow
it
to assign new values for that column, but that is not certain.
Otherwise,
you're going to be altering the design of the database, and you need to
know
what you're doing in that case.

Tom Ellison


"troy" <troy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F680A62B-FCB1-4031-A466-97DEE4987C14@xxxxxxxxxxxxxxxx
I am trying to append rows in a history query to my tables and I get the
big
old key Violations error. It is the same old 3 that keep comming up and
they
dont dissapear from the data***. This is the field that the join is
attached to with the primary key. The numbers below are the same record
ID
that keep reappearing and never go away and I get the same old error.
Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable,
DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like
[dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated






.

Quantcast