How to move a record from one line to another in Access?

[Guest]

My boss had me enter data into an Access document set up as a spreadsheet,
similar to Excel. However, now I need to move the location within the
document of some of the records (inserting line 51 above line 100, for
example).

I've never used Access like this before (mostly just done data entry in
forms), so I am really ignorant. When I try to copy or move, nothing
happens. I can't sort numerically because they have a letter at the
beginning. The Ascending or Descending sort buttons (I think) were what got
me in trouble, plus I can't move things around like in Excel.

Can anybody help? Can I maybe set up a new document and append the lines I
want until it is in the order I want?

This is a long document that is completed as far as data entry. Only the
first 1/4th or so has this problem, but I don't want to re-type any more than
I have to.

I haven't seen any help that I can understand on this. Plain talk for
dummies, please. No, I'm not really a dummie, just on some things.

Thanks,

Lola

 

[Damon Heron]

Naughty boss.
I am guessing this is actually a table or a form set up as a datasheet. In
any event, whoever set up the table should have established a primary key
with an autonumber. If so, then perhaps it is hidden from view. Right
click and unhide all columns. If you now see a numbering sequence, you can
sort on that. If not, then the question becomes why do you need to move a
line to another location? Access is different from Excel in a number of
ways, one being that the order of records doesn't really matter. You can
use queries to view the records however you want, so the input sequence is
arbitrary.

Damon

 

[John Spencer]

Add a new field to the table (fldSpecialOrder) as a number field type double

put a number in each row that indicates the position you want the record in

If you need to MOVE a row then edit the number so that the number is between
the numbers of the two rows you want to move the record to

For instance, if you want to move fldSpecialOrder 22 to a position between
13 and 14 change 22 to 13.5

SORT on fldSpecialOrder.

RECORDS in a database have no order. You can impose an order by using a
sort. To sort, you must have something to sort by.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

My boss had me enter data into an Access document set up as a spreadsheet,
similar to Excel. However, now I need to move the location within the
document of some of the records (inserting line 51 above line 100, for
example).

Well... you're learning that Access and Excel are very different programs. A
table Datasheet may look like a spreadsheet but it ISN'T.

A Table is a repository for data. It *has no order* - it's like a sack full of
records. If you want to see records in a particular order then you must, no
option, create a Query based on the table, sorting the records by some field
within the table. This query will let you edit records just like in the table;
but if you want to move a record, you need to change (manually or
programmatically) the value in the field by which you're sorting.

Also, an Access Table isn't very much like a document at all. It's - again - a
heap of records. If you want a document (to print, say) then you need to base
an Access Report on the table, and specify a sort order within the report.


John W. Vinson [MVP]