Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

how to update records in database with cells recently modified

Discussion in 'Information Technology' started by barbara Diouf, Oct 27, 2005.

  1. barbara Diouf

    barbara Diouf
    Expand Collapse
    Guest

    My name is DIOUF, from senegal but working in Johannesburg.

    I am managing drill holes data for our gold mining junior company.

    Is there a way that I can re-import records (>2500) that are already in the
    database and update the cells which have been modified since their creation?

    I dont want to delete those records in my parent table, because all the data
    in my "child" table will also be deleted. I want to save on that time.

    Thanks for your help
     
  2. Loading...


  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Barbara,

    Cheer up, quite easy! The idea is to link your external data, and use an
    Update query to update your existing records in your parent table,
    without deleting them.

    Start by linking your external data: go File > Get External Data > Link
    Tables, select the appropriate file type (Excel or Access, i would
    expect) depending on the file type of your external data, and follow the
    directions of the wizard. At the end of the process, you should have a
    new linked table in your database window, with your external data.

    Next, start making a new query (design view); add the existing table
    that you wish to update, and the linked table with the external data.
    You need to link the two, so you match corresponding records; there must
    be field in each table (DrillHoleID or something) which uniquely
    identifies each record, and this is the field on which you join the two
    tables on, by dragging the field from one table and dropping it on the
    matching field on the other (you should get a libne joining the two).
    Now drag the ID field and the field(s) that you want to update down to
    the grid (so they are displayed in datasheet view), as well as the
    corresponding fields from the external table (the latter is not required
    for the actual updating, just for viewing before you proceed!). Switch
    to datasheet view; you should see the "old" and "new" fields side by
    side. If you are happy that this has worked as expected so far, return
    to design view, and go to menu Query > Update; you should now have a new
    row in the grid, titled "Update To". In this row, you must specify the
    field in the external table to whose value each field in the original
    table will be updated to. So, if field ABC in the existing table is to
    be updated to the value of field XYZ in th external table, you should
    type [XYZ] in the Update To row under field ABC (including the square
    brackets). You need to do this only for those fielfdss in the existing
    table which are to be updated. Note: if the fields in the external table
    have the same names as the ones in the existing table, then you need to
    include the external table name (as it appears in the database window),
    so Access can tell which table's field to "read". So, the field
    reference should be something like:
    [My External Table].[FieldName]
    again, include the square brackets.
    Finally, go Query > Execute, confirm and the job is done!

    CAUTION: Always backup before you try anything of which you are not 100%
    sure!

    HTH,
    Nikos
     
  4. barbara Diouf

    barbara Diouf
    Expand Collapse
    Guest

    You made my day! thanks a lot

    "barbara Diouf" wrote:

    > My name is DIOUF, from senegal but working in Johannesburg.
    >
    > I am managing drill holes data for our gold mining junior company.
    >
    > Is there a way that I can re-import records (>2500) that are already in the
    > database and update the cells which have been modified since their creation?
    >
    > I dont want to delete those records in my parent table, because all the data
    > in my "child" table will also be deleted. I want to save on that time.
    >
    > Thanks for your help
     

Share This Page