Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi. While I have a lot of experience developing various types of Office solutions, I have one that is very new to me. I will be using SharePoint, which is a relatively new development environment for me. I'm using Office 2007 and (at least for now) working in Windows 7. The reason I mention Windows 7 is that i seem to be prompted to log in to the SP site frequently, while I'm rarely prompted to on my XP workstation.

    First let me outline the steps:
    • I have a SharePoint list that represents a cumulative list of Help Desk calls. (Technically, it's a subset of ALL calls, based on category, but that's probably irrelevant.)
    • I will periodically receive a CSV file containing up-to-date records. Many of these records may already be in the SharePoint list; I can't assume they'll all be new.
    • I need to add only the NEW records to the SharePoint list. Conceptually, this shouldn't be a problem, because they each have a unique case ID.
    What I expected to be able to do was to create a link to the SP list called "Regulatory" in Access, import the new CSV file into the TmpRegulatory table each month (replacing the previous table), and create an append query to add the new records. But for me, everything after the import of the CSV is frustratingly difficult to pull off!

    For starters, while I can make a Select query that selects only new records, it doesn't like it when I "convert" it to an append query. Honestly, I'm not even sure how the Select query works: I used the wizard, which created one that specified "IsNull" as the "WHERE", because all the records have a value... but here it is anyway:
    SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action]
    FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
    WHERE (((Regulatory.[Ref No]) Is Null));
    When I convert the SELECT query to an APPEND query, I get the error "Duplicate Output Destination '[RefNo]'. Here is the SQL for the APPEND:
    INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action], [Ref No] )
    SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action], Regulatory.[Ref No]
    FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
    WHERE (((Regulatory.[Ref No]) Is Null));

    Any ideas where I'm going wrong? Can someone explain how IsNull collects only the new records in the SELECT in the first place? Any ideas on a better way to update my SP list with the new data from the CSV?

    I really appreciate the help!
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Mesaka View Post
    When I convert the SELECT query to an APPEND query, I get the error "Duplicate Output Destination '[RefNo]'. Here is the SQL for the APPEND:
    INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action], [Ref No] )
    SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action], Regulatory.[Ref No]
    FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
    WHERE (((Regulatory.[Ref No]) Is Null));

    Any ideas where I'm going wrong? Can someone explain how IsNull collects only the new records in the SELECT in the first place? Any ideas on a better way to update my SP list with the new data from the CSV?
    Duplicate Output Destination '[RefNo] If you read the field list in the Insert Into section you will see [ref no] is listed twice. The first and the last item. Delete the 2nd one, and the corresponding entry in the select area.

    Code:
    INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action])
    SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action]
    FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
    WHERE (((Regulatory.[Ref No]) Is Null));
    The original select query joins the two tables with a Left Join, so it includes all records from tmpRegularatory, whether or not they have a matching entry in Regulatory. Where they don't have a matching entry in Regulatory, the[Ref no] field that should have come from Regulatory will be empty - Null.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for that insight! I feel ashamed that I didn't pour over the actual SQL in detail; I assumed (hah!) that things would work exactly as described in the MS reference.
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

Posting Permissions

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