Import from Excel

[Greg]

I receive 3 CDs each month. These CDs contain data in Excel spreadsheet
format. I move the columns required( copy and paste) from each CD to a new
Excel spreadsheet. I will call this spreadsheet Monthly Excel Spreadsheet.
Once some manipulations are performed upon the Monthly Excel Spreadsheet,
the spreadsheet is imported into an existing Access table(
concatenated/appended). I use the import function in Access. This has
worked well so far. There is about six months of data in the Access table.
However a problem has arisen and values in one column in the Access table
(for 1 Month) are incorrect and I would like to update these column values
through the Import function in Access without reloading all the columns. The
column to be updated is not a key or associated with any referential
integrity issues. An entire months values are incorrect! The access table
has about 2000 rows and about 300 values in the "bad" column need to be
replaced.

Can this be done?

How do you do it?

Is there any documentation as to how to do this?

Can I copy and paste to the data sheet view of the table ( I can't find a
way to do this!) ?

Are there any other suggestions on how to do this other than keying 300
values?

Thank You in advance for your patience with a neophyte.
Greg

 

[Ken Snell \(MVP\)]

It's possible to copy and paste values into a single field for multiple
records, but can be a bit tricky. Usually, one would set up the new data
into a table with the correct primary key identified, and then run an update
query to replace the current data with the new data, using the primary key
to match up.

However, if you have just 300 values, what I would do is this:

1) Make a copy of the database for backup.

2) Create a query that will select the records whose values need to be
replaced. Sort the data so that they are in the same order as in the EXCEL
spreadsheet.

3) Open the query in datasheet view.

4) Copy the cells in the EXCEL spreadsheet (within a single column) that
contain the new data. Use Ctrl+c to do this.

5) In the query's datasheet view, start at the first record, and put the
cursor at the far left of the field whose values are to be replaced. The
cursor will switch to a cross. Click the mouse, and the entire field will be
highlighted. Now, use the scroll bar to move to the last record of the
query, hold down the Shift key, position the cursor to the far left of the
field again in that last record and click the mouse. All the cells in that
"column" now should be highlighted.

6) Press Ctrl+v to paste the data. ACCESS should ask if you want to update
the data; answer yes.

 

[Greg]

Ken,
Thank You,

I used your suggestion and it worked beautifully!
I have much to learn and your suggestions helped in 2 ways.. Solving a
problem and educating a neophyte.
Greg

 

[Ken Snell \(MVP\)]

You're welcome.