Welcome to SPN

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

Sign Up Now!

Update table in access

Discussion in 'Information Technology' started by Carol, Jul 28, 2006.

  1. Carol

    Carol
    Expand Collapse
    Guest

    I have a table with a large amount of information that my database has been
    created on. I need to update certain parts of the table with new data. I am
    told I have to enter all the data by hand. However, the data is available on
    an Excel spreadsheet. Sounds like hand entering information defeats the
    purpose of a this type of database. I know how to export the data to a new
    table, but I can't figure out how to get the new data onto the existing
    table. Help!
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Carol,

    What is the structure of your Excel sheet? What is the
    structure of your table? Is what you are wanting to
    add/change records in one Access table or are they in
    multiple tables?

    You can use an APPEND query to add records and/or an UPDATE
    query to change records

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Carol wrote:
    > I have a table with a large amount of information that my database has been
    > created on. I need to update certain parts of the table with new data. I am
    > told I have to enter all the data by hand. However, the data is available on
    > an Excel spreadsheet. Sounds like hand entering information defeats the
    > purpose of a this type of database. I know how to export the data to a new
    > table, but I can't figure out how to get the new data onto the existing
    > table. Help!
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Carol wrote:
    > I have a table with a large amount of information that my database
    > has been created on. I need to update certain parts of the table
    > with new data. I am told I have to enter all the data by hand.
    > However, the data is available on an Excel spreadsheet. Sounds like
    > hand entering information defeats the purpose of a this type of
    > database. I know how to export the data to a new table, but I can't
    > figure out how to get the new data onto the existing table. Help!


    You can update Access data from data in Excel, but it must meet certain
    criteria. Basically it the Excel file need to have information in it to
    match the rows in Excel to the records in Access. The data must be discrete
    in Excel and Access (ideally a full cell equals a full field) and there must
    be a one to one relationship for all the matched records/rows.

    --
    Joseph Meehan

    Dia duit
     
  5. mr bho

    mr bho
    Expand Collapse
    Guest

    Hi Carol,

    I have already faced something similar. I am sure there are better ways
    but if it is a rare one- time measure you can Copy and paste from Excel
    to Access. Open both files, copy each column from Excel and paste to
    the corresponding column in Access. If you open the Access table in
    data entry mode the pasted data just append to your existing data. This
    method has, however, serious limitations depending on your data -
    especially in formatted date fields with input mask.


    DIYKID


    Carol wrote:

    > I have a table with a large amount of information that my database has been
    > created on. I need to update certain parts of the table with new data. I am
    > told I have to enter all the data by hand. However, the data is available on
    > an Excel spreadsheet. Sounds like hand entering information defeats the
    > purpose of a this type of database. I know how to export the data to a new
    > table, but I can't figure out how to get the new data onto the existing
    > table. Help!
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Carol,

    although you can use the data in Excel from Access, since
    you are new to this, you probably want to import the excel
    data and make a table in Access to work with

    from the menu, choose

    File, Get External Data, Import...

    change the file type to XLS and navigate to your workbook

    then follow the wizard instructions to bring it in

    once in Access, you can use Update and Append queries --
    when you get to this step, if you need more help, please
    specify your data structure in Access and exactly what you
    are trying to do

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    strive4peace wrote:
    > Hi Carol,
    >
    > What is the structure of your Excel sheet? What is the structure of
    > your table? Is what you are wanting to add/change records in one Access
    > table or are they in multiple tables?
    >
    > You can use an APPEND query to add records and/or an UPDATE query to
    > change records
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Carol wrote:
    >
    >> I have a table with a large amount of information that my database has
    >> been created on. I need to update certain parts of the table with new
    >> data. I am told I have to enter all the data by hand. However, the
    >> data is available on an Excel spreadsheet. Sounds like hand entering
    >> information defeats the purpose of a this type of database. I know
    >> how to export the data to a new table, but I can't figure out how to
    >> get the new data onto the existing table. Help!
     
  7. Carol

    Carol
    Expand Collapse
    Guest

    Keep talking to me. I have met all of the criteria you list. My data in
    Excel contains about 30 records compared to the data in Access has about 275.
    Could that be the problem? How do I update given I have all of the criteria.

    "Joseph Meehan" wrote:

    > Carol wrote:
    > > I have a table with a large amount of information that my database
    > > has been created on. I need to update certain parts of the table
    > > with new data. I am told I have to enter all the data by hand.
    > > However, the data is available on an Excel spreadsheet. Sounds like
    > > hand entering information defeats the purpose of a this type of
    > > database. I know how to export the data to a new table, but I can't
    > > figure out how to get the new data onto the existing table. Help!

    >
    > You can update Access data from data in Excel, but it must meet certain
    > criteria. Basically it the Excel file need to have information in it to
    > match the rows in Excel to the records in Access. The data must be discrete
    > in Excel and Access (ideally a full cell equals a full field) and there must
    > be a one to one relationship for all the matched records/rows.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  8. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Carol wrote:
    > Keep talking to me. I have met all of the criteria you list. My
    > data in Excel contains about 30 records compared to the data in
    > Access has about 275. Could that be the problem? How do I update
    > given I have all of the criteria.


    Sorry about the delay getting back, I lost the thread.

    No that should not be a problem.

    What is the Access field that you are using for the relationship?

    How about a few examples of the data in those fields? (Both from Excel
    and Access)

    Assuming you are not doing this many times a day, I would import the
    data from Excel and then make an update query using the imported data table
    to update the existing table.


    >
    > "Joseph Meehan" wrote:
    >
    >> Carol wrote:
    >>> I have a table with a large amount of information that my database
    >>> has been created on. I need to update certain parts of the table
    >>> with new data. I am told I have to enter all the data by hand.
    >>> However, the data is available on an Excel spreadsheet. Sounds like
    >>> hand entering information defeats the purpose of a this type of
    >>> database. I know how to export the data to a new table, but I can't
    >>> figure out how to get the new data onto the existing table. Help!

    >>
    >> You can update Access data from data in Excel, but it must meet
    >> certain criteria. Basically it the Excel file need to have
    >> information in it to match the rows in Excel to the records in
    >> Access. The data must be discrete in Excel and Access (ideally a
    >> full cell equals a full field) and there must be a one to one
    >> relationship for all the matched records/rows.
    >>
    >> --
    >> Joseph Meehan
    >>
    >> Dia duit


    --
    Joseph Meehan

    Dia duit
     

Share This Page