Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: append query not appending to all records

    I have several tables for student information, divided accordingly to the fields each would contain. The only common fields that the tables share is the primary key field that contains the student ID and the second field for the student's name.

    My main student list table has several entries which I would like to populate the other tables with. I created an append query for the function. I was able to successfully use the query to duplicate the student name field but was unable to do the same with the ID. Some of the records have the name and ID, but most only have the name.

    The error I get is "didn't add XX record(s) to the table due to to key violations". That narrows it down to the pk field.

    The source field (SID) is a primary key field, so I am certain that it does not have duplicates. The target field (dir_SID) is a PK field, and my first assumption was that it does not allow this kind of transfer or my field name was a reserved word. However, since several records of dir_SID was properly appended to, I am at a loss as to why only certain records were okay.

    My append query is as follows:
    field: SID / Student_Name
    Table: list_students / list_students
    append to: dir_SID / dir_name

    No 'criteria' and 'or' conditions

    The target table is sdt_Directory, it has several fields and no existing records. Source table list_Students has 61 records. SID and dir_SID are both primary key fields, no masks, validation rules and allows zero length.

    Thanks!

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    In a relational database, we try not to duplicate data. One table should have the Student information (Name, age, etc.) and the other tables should only contain the Student Table PrimaryKey as a ForeignKey and none of the other data from that table. The name can easily be "included" in any recordset using a query that joins the tables.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with RG.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2008
    Posts
    163
    I changed the whole name thing. The append query is still being selective.

    Oh yeah, I'm currently in production phase in my db. Since I'm testing it as I go along, I input actual data into some of the tables already. Source table has actual data. The target table is newly made.

    I can just copy the records directly =p But I wanted to do it with an append query, I guess its like practice. I'm totally new to access (I only touch this thing for a couple of hours or so each workday), so every bit helps!

    New changes are:
    -target table and append query no longer has student name field
    -dir_SID in the target table is no longer a pk field, if it is populated properly, it will serve as a foreign key for SID

    I tried the following:
    -dir_SID and SID has relationship (validation rule violations -which is weird since field dir_SID has no required validations)
    -no relationship (same error, validation rule violation)
    -SID is not a PK (same error, validation rule violation)
    -no criteria (same error, validation rule violation)
    -criteria is null and "" (updates zero records)

    target table, sdt_directory is still has zero records.

    Does anyone know why the append query is being selective? =(
    Last edited by coffeecat; 07-14-08 at 01:40.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, give me some clarity here.... detail what the structure is of the source table and the structure of the destination table. Then post here your SQL for your append query and I'll do my best to help you.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    Err.. Its working. I'm not sure what the concept behind the solution is: In the design view, I pushed the target field down one tier (some other field went on top). o_o'

    I remembered the earlier situation where I was able to append to the (now removed) dir_name field, and wondered if the fact that the name field was in a lower position had anything to do with it. Anyway, if anyone runs into this, or can share some insight as to what the logic behind this is, here's the info:

    Um, I'm not sure what structure means. I'll try my best to answer, let me know what information I'm missing-

    source table: list_students (primary key: SID, foreign: section_code)
    fields: five fields total, only SID is used in the query
    SID: (size: 50 / type: text / required: no / allow zero length: yes / indexed: yes -no dupes / unicode: yes / ime: none)

    destination table: sdt_directory (primary key: none, foreign: SID)
    fields: eight total, only dir_SID is used in the query
    dir_SID: (type: text / size: 50 / required: no / allow zero length: yes / indexed: no / unicode: yes / ime: none)
    Except for SID and dir_SID, there are no common fields in the source and destination tables.

    I create my queries using the design view, how do I check the SQL of my query? thanks!

    I'm guessing this has something to do with the fact that my destination table has no primary key field. Anyway, the append query simply won't append to the first field properly.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In the query in question, View - SQL View.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jun 2008
    Posts
    163
    ah, thanks again!

    SQL of query is as follows:

    INSERT INTO Sdt_directory (dir_SID)
    SELECT [list_students].SID
    FROM [list_students];

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so all looks good.

    Considering the error you are getting, it has to be that there is a field in the destination table that is set to Yes (No Duplicates) in the Indexed property... one of the other 7 fields that you're not telling me about

    Can you open the sdt_directory table, and enter in ONLY a dir_SID and save the record?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jun 2008
    Posts
    163
    ...I checked all the other fields, they're all text data with default settings (indexed: no) O__o'

    Can you open the sdt_directory table, and enter in ONLY a dir_SID and save the record?
    table view? yep, it accepted any alphanumeric gibberish I typed in.

    Ah, I forgot to mention, the other fields are empty, they're meant to have data (phone numbers, addresses, etc), but since I have not created the form that will input data for my sdt_directory table, I haven't filled in anything. Only the dir_SID field has data.

    Anyway, I tried returning the fields to its old arrangement, with dir_SID in the first column. I deleted all the existing records and tried the append query again. This time it worked. I guess I accidentally did something wrong and accidentally fixed it. >_>
    Last edited by coffeecat; 07-15-08 at 04:04.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Meh, oh well, so long as it's fixed. Pity we couldn't isolate the problem though.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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