Results 1 to 13 of 13
  1. #1
    tkosel is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    23

    Append Query Not Working as expected

    I have a Windows 10, Access 2016 DB with a weird issue.
    I have a table named PM Tasks with the following properties.
    [IMG]file:///C:/Users/tom/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]Click image for larger version. 

Name:	capture1.png 
Views:	27 
Size:	40.0 KB 
ID:	44353

    I want to append some records to this table, and have the following query to do so.

    Code:
    INSERT INTO PMTasks ( EquipmentKey, MaintenanceCycle, AssignedResponsibility, Source, PMInstruction, Comment, PMTaskName, CreationDate )
    SELECT PMTasksTemp.EquipmentKey, PMTasksTemp.MaintenanceCycle, PMTasksTemp.AssignedResponsibility, PMTasksTemp.Source, PMTasksTemp.PMInstruction, PMTasksTemp.Comment, PMTasksTemp.PMTaskName, PMTasksTemp.CreationDaste
    FROM PMTasksTemp;

    When I run the above append query, I get the following error.

    [IMG]file:///C:/Users/tom/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg[/IMG]Click image for larger version. 

Name:	capture2.jpg 
Views:	23 
Size:	56.7 KB 
ID:	44354

    The Primary key is KEY, a auto number field, which as I understand it, should be automatically numbered by the append query. I do not understand why there would be key violations here.


    Can anyone help me with this?

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    830
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,660
    Do you have any other fields in the table design set for "no duplicates" like maybe EquipmentKey?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,230
    Create same query without INSERT clause, modify it to aggregate one counting same set of columns, and set the HAVING clause for this count to be >1. In case you really have multiple rows in PMTasksTemp for same set of values, you get them, and can analyze PMTasksTemp for reasons.

  5. #5
    tkosel is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    23

    Thanks for you assistance.

    Quote Originally Posted by moke123 View Post
    Thanks for the important information. I changed the field names. However, that did not solve the issue. I should have mentioned that this is a split front end, back end application.

    After more experimentation, discovered that if I bring the PMTask table into the front end, don't use a linked table, the query works fine. I have compacted and repaired both the front end and back end.

    I desire to use a linked table, but if I do, the query fails. Any help?

  6. #6
    tkosel is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    23
    No, there are no other fields that are indexed with no Duplicates.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,660
    Please post both FE and BE here for analysis.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,604
    The message is pretty clear. Two records cannot be appended because some key value already exists in a table. Whether that is a PK autonumber field, or a field set to 'Indexed - no duplicates' or some other PK field is what I think you'll find. I realize these possibilities have already been mentioned - I'm just seconding them.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,263
    Two things I am curious about:

    Misspelled field name: PMTasksTemp.CreationDaste - shouldn't this be "Date"?

    Shouldn't there be a FROM clause???

    Code:
    INSERT INTO PMTasks ( EquipmentKey, MaintenanceCycle, AssignedResponsibility, Source, PMInstruction, Comment, PMTaskName, CreationDate )
    SELECT PMTasksTemp.EquipmentKey, PMTasksTemp.MaintenanceCycle, PMTasksTemp.AssignedResponsibility, PMTasksTemp.Source, PMTasksTemp.PMInstruction, PMTasksTemp.Comment, PMTasksTemp.PMTaskName, PMTasksTemp.CreationDaste FROM PMTasksTemp;
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    830
    Have you corrected your typo?
    Code:
    PMTasksTemp.CreationDaste
    edit: lol. Too late steve beat me.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    tkosel is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    23
    Quote Originally Posted by moke123 View Post
    Have you corrected your typo?
    Code:
    PMTasksTemp.CreationDaste
    edit: lol. Too late steve beat me.
    Thanks to all for suggestions. I realize there are typos, but they are not causing this issue. I have solved the issue. PMTasks is a child table. Before I can append records to it, the Parent Table needs to have a related record. I have done this and everything is fine. Thanks for all your help.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,604
    Before I can append records to it, the Parent Table needs to have a related record.
    That is also a key violation. You'd think that the message You cannot add or change a record because a related record is required in table... would be more helpful. Or maybe you would have gotten that message if you had chosen to append anyway, but I don't know. You didn't say whether your response was yes or no.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  13. #13
    tkosel is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    23
    Quote Originally Posted by Micron View Post
    That is also a key violation. You'd think that the message You cannot add or change a record because a related record is required in table... would be more helpful. Or maybe you would have gotten that message if you had chosen to append anyway, but I don't know. You didn't say whether your response was yes or no.
    I agree, the message you proposed, "You cannot add or change a record because a related record is required in table... would be more helpful."

    My response to the error was yes, I wanted to run it anyway. No additional error was thrown, the records were not appended.

    Anyhow, the issue is solved, I should have known better, Just took me a while to get there.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 12-27-2018, 10:41 AM
  2. Append query not working
    By rayted in forum Queries
    Replies: 1
    Last Post: 03-05-2018, 05:22 AM
  3. Replies: 3
    Last Post: 09-30-2017, 10:55 AM
  4. Replies: 14
    Last Post: 04-22-2015, 06:57 PM
  5. Append query not working
    By seth.murphine in forum Queries
    Replies: 5
    Last Post: 04-20-2012, 06:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums