Results 1 to 3 of 3
  1. #1
    pradeepkumar089 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    5

    Update table using query

    Hi, I have simple master table which has Name, Score for Dec-19, Jan-20, Feb-20, Mar-20, April-20 and May-20 and i will receive new file end of June-20 month with updated score for last 6 months which will have data for Jan-20, Feb-20,Mar-20, Apr-20, May-30 and Jun-20.



    I need a query to update the master file by including June data from new file along with upadting Jan-20, Feb-20,Mar-20, Apr-20 and May-30 data.

    My expected result in Master table would be Name, Dec-19, Jan-20, Feb-20, Mar-20, April-20, May-20 and June-20.

    Please help to create query to solve the above issue

    Thank you in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you get it in Excel, i would save the XL file to the same place evertime like: c:\temp\File2Import.xlsx
    attach it as an external table to use in the query.
    build your append query to add the data to the master table.
    put the query in a macro: mImportXL

    then the steps will be:
    1. save new xl file to c:\temp\File2Import.xlsx
    2. run macro
    done.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you put the brakes on and fix the issues with your dB.

    "Name" is a reserved word in Access - plus it is not descriptive. Name of What???
    Your table is designed as an Excel spreadsheet.
    This happens so often it has a name - "Committing Spreadsheet".

    Spreadsheets are typically short and wide. Access tables are typically tall and narrow.




    Consider a table design of

    tblSomeTable
    ===============
    SomeID_PK (Autonumber - PK)
    SomeName (Text)
    SomeDate (DateTime)
    SomeScore ( Number - Double)


    In your current design (Excel spreadsheet), every time you need to add a new month, you have to change the table design by adding a new field.
    In a normalized Access table, you just add a new record. The table design does not change.

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

Similar Threads

  1. Update Query with Criteria in update to table
    By tanner.morgan in forum Queries
    Replies: 3
    Last Post: 08-28-2018, 07:56 AM
  2. Replies: 3
    Last Post: 06-01-2017, 06:57 AM
  3. Replies: 4
    Last Post: 04-27-2016, 04:36 PM
  4. Replies: 1
    Last Post: 05-17-2013, 12:53 PM
  5. Replies: 1
    Last Post: 02-08-2012, 04:50 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