Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Key Violations in Append Query (Office 2000, Win X

    I am trying to run an append query, and I am getting a key violation message for all the records I am attempting to append.

    I did a search and have thoroughly read <post#=224225>post 224225</post#> (and I think I understand it) but I am still getting the error message.

    I am trying to append data from T_Results to T_ResultDetails.
    I want to append:
    [T_Results.NewID] to [T_ResultDetails.SurveyID]
    [T_Results.Wages] to [T_ResultDetails.Rating]

    My table T_ResultDetails has five fields and there is currently no data in it.
    [DetailID] is an autonumber primary key for the table.
    [SurveyID] is a number field, and is linked one-to-many with referential integrity to [T_Results.NewID]. In T_Results, the field [NewID] is an autonumber primary key.
    [Section] is a text field not linked to anything.
    [Item] is a text field linked one-to-many with referential integrity to another table's text primary key. I have [T_ResultDetails.Item] set with a default of the appropriate item primary key (in this case, 1A).
    [Rating] is a text field - as is [T_Results.Wages] which I am attempting to append to it.
    In T_ResultDetails, only the primary key field is set for no duplicates, although [SurveyID] is indexed with duplicates okay.

    I have tried deleting table relationships (and when that didn't help I linked them back up), I added the default value [Item] so it wouldn't create blank fields, I have checked my data types and unless I'm missig something they appear to be compatible, I compacted the database, and I am now fresh out of ideas.

    (in case anyone wonders why I'm trying to append data from one table to another that is linked to it, it's because I'm trying to move most but not all of the data from a table that was set up as a flat-file for easy data-entry by volunteers into a relational format that I can actually query without turning myself inside out . Most of the tables and queries in this database were imported from one I built three years ago which worked fine. I have made only minor changes, and I can't see that any of them would affect what I am trying to do here).

    Can anyone suggest something else I can try?

    Thank you,
    -cynthia

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Key Violations in Append Query (Office 2000, Win X

    One possibility that comes to mind is that you are trying to append a value to the [DetailID] field - that will always cause a failure. Otherwise I would suspect the referential integrity links - did you remove all of them, or just one at a time?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Key Violations in Append Query (Office 2000, W

    I have only the two fields in my append query - [NewID] and [Wages] going into [SurveyID] and [Rating] respectively (I have in the past made the error of trying to append into an autonumber field. Oops! but not this time).

    On the relationships - there are only two at the table level in this database. I first deleted the one between T_Labels (which links to [T_ResultDetails.Item] ) and T_ResultDetails, then when that didn't help I also deleted the one between T_ResultDetails and T_Results. That didn't help either, so then I added that one back in (and at that point set the default in [T_ResultDetails.Item] to "1A") and then added back the one between T_Labels and T_ResultDetails.

    I was leery, after reading the post mentioned in my original post on this thread, of leaving them off because I didn't want to violate the referential integrity when I turned them back on. But I did want to see if it would work with them off - it didn't.

    I have also tried this with substituting various other fields in the query for [Wages], as I'll have to append about fifteen of them once I get this thing working. But none of them work.

    I should perhaps mention that the original database was created in Access97 and I imported the tables and queries into Access2000 - but it also didn't work when I created a totally fresh query in Access2000 (I did NOT try creating totally fresh tables but I had earlier today appended data from five different tables to T_Results without difficulty - have not yet successfully appended anything to T_ResultDetails)

    -cynthia

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Key Violations in Append Query (Office 2000, W

    Can you post a cutdown version of your database.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Key Violations in Append Query (Office 2000, W

    I can see no logic to this - but it works now, and here is what I did:
    I made a backup copy of the database (and I tried to run the query in the backup - it didn't work).
    Then in the original database I renamed the two tables in question and copied them, giving each copy the original name. So now, I have a table called T_Results which is an exact copy (cut and paste) of what is now T_ResultsOriginal.
    Then I made a new query. Just like the first one. I ran it. It worked!

    Then I relinked the relationships between the new tables (I had to delete the relationships when I renamed the tables). No problems there, either.

    I compared the two queries and they appear to be identical (there is only one table and only two fields in them - can I really be missing something?). I ran the query on two more fields (by substituting them for [Wages] in the query). It still worked. Then, I ran the original append query (using a different field since I had already appended [Wages]) and IT WORKED TOO. I had previously tried it with at least three different fields.

    So why did it work with exact copies of the tables but wouldn't work with the original tables? I have a feeling there is not a logical answer to this - but at least now I can get on with the project.

    -cynthia

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Key Violations in Append Query (Office 2000, W

    One possibility is that there is a corrupt index on your original table that causes things to choke when you try to run the append. Creating a new table that is a copy recreated the index, and then things worked. That's about the only logical explanation I can come up with. Otherwise it's probably <img src=/S/alien.gif border=0 alt=alien width=14 height=15> <img src=/S/devil.gif border=0 alt=devil width=15 height=15> <img src=/S/devil.gif border=0 alt=devil width=15 height=15> <img src=/S/devil.gif border=0 alt=devil width=15 height=15> s!
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Key Violations in Append Query (Office 2000, W

    Looks like you were posting that request just about the same time I was posting the (albeit weird) explanation of having fixed it. Wendell's explanation to my fix seems plausible to me. If your curiousity is totally whetted and you would like to examine a cutdown version of the database, I'll be happy to make one and post - but if you are on to other things now that this dilemma appears to be solved we'll let it go. Just let me know.

    -cynthia

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Key Violations in Append Query (Office 2000, W

    Let's let it go, what's the point of chasing aliens .................

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Key Violations in Append Query (Office 2000, W

    Agreed!

    -c

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •