Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2006
    Posts
    73

    Unanswered: Append Query not Appending

    Good Morning guys,

    Hope all you Americans are enjoying your Memorial Day..

    Well anyway my problem that's arised is the following.

    I'm using Access 2003, and with my append query I'm writing it's not exactly appending.

    What I'm doing is taking information from a table called Service Hours, and trying to append it to a linked table with the exact same information in it. So I know it's got nothing to do with the information on either table not matching up.

    This is the SQL statement I have for the table.

    INSERT INTO [Service Hours2] ( ServiceRecordID, EmployeeID, [Date], [Travel Start], [Plant In], [Plant Out], [Travel End] )
    SELECT [Service Hours].ServiceRecordID, [Service Hours].EmployeeID, [Service Hours].Date, [Service Hours].[Travel Start], [Service Hours].[Plant In], [Service Hours].[Plant Out], [Service Hours].[Travel End]
    FROM [Service Hours]

    And what it says is..

    It cannot append all the records it's set 0 field(s) to Null due to a type conversion failure and didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations and 0 record(s) due to validation rule violations.

    Now before ya ask..yes all the data types are the same, there shouldn't be any difference between any of the information. None of the records I'm trying to append are autonumbers or a Primary Key so I don't understand where exactly things are going wrong. So if anyone can help out that'd be great.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a key violation is a key violation

    doesn't have to be in a primary key, could be in a unique key too

    go, take a look at the table definition, you'll find the duplication soon enough
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    73
    would I still be getting it if duplicates are allowed though? cause there's times where it could be two different people on the same ID just different dates. so the ID number would be the same..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you still be getting a key violation error if duplicates are allowed? of course not

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    73
    still not working and all are allowing duplicates(had to double check on that one)..

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are all keys single columns? any composite keys?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2006
    Posts
    73
    everything seems to be in order for it to allow me to append..I'll toss up a sample DB with just those two tables linked..and the query as well..let ya have a look through it see if ya can figure it out..cause I'm beyond baffled by this lol

  8. #8
    Join Date
    Mar 2006
    Posts
    73
    Ok here it is..

    all ya have to do is link the table from testAppend2 to testAppend..
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, no, that's not all i have to do

    i can't open your databases, so i would also have to go out and purchase an upgrade to access, and i'm not ready to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2006
    Posts
    73
    what version of Access are ya usin? 2000?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm using access 97
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2006
    Posts
    73
    ok try these ones then..converted them back to 97 file format..
    Attached Files Attached Files

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i can see those two tables fine

    my next question is, they both have the same name, how come?

    and they both have the same exact rows, why? did you already run the INSERT? because it looks like it worked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2006
    Posts
    73
    the reason for this is because here we have our service guys who are out on the road almost every week for 2-3 days at a time or more..so..they update a local version of the said databases, and come back to the office to insert their data into the server version back at the office.

    So what we're trying to do is have it so instead of enterting in their data twice(once on their copy, and then again when they get back) they click a button on a form, and get it to update/append the new data.

  15. #15
    Join Date
    Mar 2006
    Posts
    163
    Smythe100

    I downloaded your first attachment, linked the table and ran the query.

    It worked fine and inserted 291 records.

Posting Permissions

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