Access changes sort order on paste

[lee_alkureishi]

Hi all,

I'm quite new to access, and have a pretty straightforward problem (I hope):

I have a table with 300 records in 10 columns. I want to:
1. filter the data based on column A (gives me 150 visible records)
2. Copy from an excel spreadsheet which exactly matches the access table (single column only, 150 rows)
3. Paste into a new column I created in the access table

Unfortunately, when I hit paste in access, it loses the filter and pastes the data into the complete dataset (i.e. into the wrong records).

I've no idea why access won't keep the filter I set. I also tried numbering the records and sorting them based on this new column. Again, Access lost the sort order before pasting the data.

Help!

I really need to be able to paste my excel data into the table in access!

Can anyone shed some light for me?

Thanks in advance,

Lee

 

[Sum Guy]

You have crossed a threshold. You are no longer dealing with Excel spreadsheets, you are now dealing with Access tables.

On the Access top menu, select File and choose Get External Data.
Select Import
Import your Excel spreadsheet. This should work OK as you have stated the spreadsheet is set up the same way as the Access table.
When the spreadsheet is imported, you will have to create an Append Query. This will append the data in the spreadsheet to the table.
The indexes and sorting you have pre-determined for the table will be complied with when you import the spreadsheet data into the table.

 

[lee_alkureishi]

Thanks

Thanks for the reply.

That particular column is set up the same way as the access spreadsheet (i.e. the same sort order), but it's part of a larger spreadsheet with multiple columns i'd like to transfer to access. They're not all contiguous either, so using the import data feature could be tricky without extensive re-structuring of the spreadsheet.

But, I figured out a way to stop it from un-sorting the data. Don't know why it works, but it does so I'm not complaining!

My method:
Highlight all 150 records in the column I'd like to replace the data in (Access)
Switch to excel - highlight the 150 updated records and press ctrl+c
Go back to access - scroll up to top
right-click on first record (still highlighted), click paste
Click yes to confirm paste of 150 records

For some reason, this maintains the sort order and pastes the data into the sorted table. No idea why any of the other copy-paste methods don't do that...ah well....

Thanks again,

Lee

 

[Pat Hartman]

When you use Access as if it were a spreadsheet you can expect to be surprised and disappointed. I would never trust the method you have settled on. You should import the spreadsheet and then create a query that joins the imported spreadsheet to the table you want to update and run an update query.

 

[chemturion]

I agree: frustrating. However, simply create a new table in Access (let's call it tblTEMP). Paste your values from Excel into tblTEMP (perhaps we'll call the field, fldTEMP). Copy fldTEMP and paste into your desired field in desired table. No importing - no sorting against our will.