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

    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:	20 
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:	17 
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
    772
    If this helped, please click the star at the bottom left and add to my reputation- Thanks

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,593
    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,217
    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
    11

    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
    11
    No, there are no other fields that are indexed with no Duplicates.

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

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,199
    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 online now Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,194
    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
    772
    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

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