Results 1 to 6 of 6
  1. #1
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47

    Using Update Query to adjust data on a tabel, taken from a linked excel sheet

    Hi there, I was wondering if i could obtain any assistance for the following.

    I am trying to create an update query for my current database. I need the update query to contrast and compare information from a linked excel sheet (read-only).

    I have successfully created the query, yet I continually get the return of "0 fields were updated" even when the linked excel sheet displays the changes.

    Any assistance with either a work around or solution would be awesome!

    Cheers



    Boost

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post the query SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Apologies for the large data dump... here's some background information.

    The data is being pulled off of SAP, where it is exported to an Excel sheet for convenience. This export occurs approx. every 48hrs.

    I then plan on pulling this excel sheet into Access, through the method of a linked table (IE. data changed in excel sheet from SAP occurs in Access linked table).

    The linked table is 'not update-able'.

    I want to add other record-sets/columns to the data held in the excel sheet from within Access. I know this can't be done, so what I have tried to do is create a second table with the appended data from the linked excel table.

    Ideally, I am now trying to run an update query that compares and updates the two tables (Imported,linked, Excel sheet) & (Native, Access Table) and applies the changes that are made to the excel sheet circa 48hrs. to that of the native, Access table.

    Below is the SQL view of my Update Query between the linked datasheet from excel (at this time named Sheet1) and the native held access table (Students).

    The Students table has the exact same column headings and data-types as the excel sheet with the exception of a established primary key in the excel sheet.
    [CODE]Apologies for the large data dump... here's some background information.

    The data is being pulled off of SAP, where it is exported to an Excel sheet for convenience. This export occurs approx. every 48hrs.

    I then plan on pulling this excel sheet into Access, through the method of a linked table (IE. data changed in excel sheet from SAP occurs in Access linked table).

    The linked table is 'not update-able'.

    I want to add other record-sets/columns to the data held in the excel sheet from within Access. I know this can't be done, so what I have tried to do is create a second table with the appended data from the linked excel table.

    Ideally, I am now trying to run an update query that compares and updates the two tables (Imported,linked, Excel sheet) & (Native, Access Table) and applies the changes that are made to the excel sheet circa 48hrs. to that of the native, Access table.

    Below is the SQL view of my Update Query between the linked datasheet from excel (at this time named Sheet1) and the native held access table (Students).

    The Students table has the exact same column headings and data-types as the excel sheet with the exception of a established primary key in the excel sheet.

  4. #4
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    UPDATE Sheet1 INNER JOIN Students ON Sheet1.SVC = Students.SVC SET Students.SURNAME = [Sheet1].[SURNAME], Students.UNIT = [Sheet1].[UNIT], Students.[SUB UNIT] = [Sheet1].[SUB UNIT], Students.ARM = [Sheet1].[ARM], Students.[SUB GROUP] = [Sheet1].[SUB GROUP], Students.[ENLST DATE] = [Sheet1].[ENLST DATE], Students.[ENGT EXPY] = [Sheet1].[ENGT EXPY], Students.LOS = [Sheet1].[LOS], Students.[BAND] = [Sheet1].[BAND], Students.[BAND SENY] = [sheet1].[BAND SENY], Students.[RANK SENY] = [Sheet1].[RANK SENY], Students.[2ND CIT] = [Sheet1].[2ND CIT], Students.[SY CLAS] = [Sheet1].[SY CLAS], Students.[EXPY DATE] = [sheet1].[EXPY DATE], Students.[LCFT RESULT] = [Sheet1].[LCFT RESULT], Students.[CONDUCT DATE] = [Sheet1].[CONDUCT DATE], Students.[EXPY DATE1] = [Sheet1].[EXPY DATE1], Students.[RFL RESULT] = [sheet1].[RFL RESULT], Students.[RESULT CAT] = [sheet1].[RESULT CAT], Students.[CONDUCT DATE2] = [sheet1].[CONDUCT DATE2], Students.[EXPY DATE3] = [sheet1].[EXPY DATE3], Students.[BWST RESULT] = [sheet1].[BWST RESULT], Students.[CONDUCT DATE3] = [Sheet1].[CONDUCT DATE3], Students.[EXPY DATE4] = [sheet1].[EXPY DATE4], Students.[MED DATE] = [Sheet1].[MED DATE], Students.HF = [Sheet1].[HF], Students.AF = [Sheet1].[AF], Students.GF = [Sheet1].[GF], Students.ZF = [Sheet1].[ZF], Students.[NEXT EXAM DATE] = [Sheet1].[NEXT EXAM DATE], Students.[DENTAL DATE] = [Sheet1].[DENTAL DATE], Students.[NATO CAT] = [Sheet1].[NATO CAT], Students.[DENTAL OR] = [Sheet1].[DENTAL OR], Students.[NEXT EXAM DATE1] = [Sheet1].[NEXT EXAM DATE1], Students.[INNOC STATUS] = [Sheet1].[INNOC STATUS], Students.[EXPY DATE5] = [Sheet1].[EXPY DATE5], Students.[PASS#] = [Sheet1].[PASS#], Students.[EXPY DATE6] = [Sheet1].[EXPY DATE6], Students.[ID CARD #] = [Sheet1].[ID CARD #], Students.[EXPY DATE7] = [Sheet1].[EXPY DATE7], Students.[ID DISCS] = [Sheet1].[ID DISCS], Students.LOAC = [Sheet1].[LOAC], Students.IEDAT = [Sheet1].[IEDAT], Students.[MJTP-L1] = [Sheet1].[MJTP-L1], Students.[MJTP-L2 EXPY] = [Sheet1].[MJTP-L2 EXPY], Students.LIC1 = [Sheet1].[LIC1], Students.[LT 4WD] = [Sheet1].[LT 4WD], Students.[DFM-BASIC] = [Sheet1].[DFM-BASIC], Students.[DFM-ADV] = [Sheet1].[DFM-ADV], Students.[DFM-BC] = [Sheet1].[DFM-BC], Students.[DFM - ADV COMD] = [Sheet1].[DFM - ADV COMD], Students.[DFM - RES] = [Sheet1].[DFM - RES], Students.[STEYR - AWQ] = [Sheet1].[STEYR - AWQ], Students.[EXPY DATE8] = [Sheet1].[EXPY DATE8], Students.[PISTOL - AWQ] = [Sheet1].[PISTOL - AWQ], Students.[EXPY DATE9] = [Sheet1].[EXPY DATE9], Students.GRENADE = [Sheet1].[GRENADE], Students.[EXPY DATE10] = [Sheet1].[EXPY DATE10], Students.[TRG DAYS] = [Sheet1].[TRG DAYS], Students.CIT = [Sheet1].[CIT];



    Apologies, above is the SQL view

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Are Sheet1.SVC and Students.SVC of same format (e.g numeric vs. numeric, or text vs. text, no trailing/leading spaces in any of them, etc.). Inner join returns a records set where is exact match between joining keys in both tables. When no record is updated, then it means there were no records in those tables with such match.

    Btw, your table looks as highly un-normalized (9 expire date fields, several conduct date, next exam date, and MJTP's fields, etc.). I think you have to split it into several tables instead
    Last edited by ArviLaanemets; 05-24-2018 at 01:54 AM.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't see anything wrong with the SQL and cannot replicate issue. Suggest you test with a much reduced SQL, just a couple of fields, test UPDATE, add couple more fields, repeat until the query fails.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2017, 02:29 PM
  2. Replies: 5
    Last Post: 11-18-2016, 05:40 PM
  3. Deleting Linked Excel sheet
    By BatmanMR287 in forum Access
    Replies: 4
    Last Post: 08-07-2015, 12:28 PM
  4. Replies: 1
    Last Post: 04-10-2012, 12:34 PM
  5. Replies: 0
    Last Post: 02-21-2008, 09:52 AM

Tags for this Thread

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