Update table in access

[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!

 

[strive4peace]

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

*

 

[Joseph Meehan]

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.

 

[mr bho]

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

 

[strive4peace]

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

*

 

[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]

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.