inserting record in the middle of table (1 Viewer)

[quest]

Hi,
I need to insert record in the middle of table. I do it with copy paste. that way I get two same records of which I use one to edit and make it new record. Can that be done automatically. I don't know coding.
thanks

 

[Minty]

There is no "middle" of a table in Access.
The data tables have no intrinsic sort order they are just displayed in whichever order Access decides to show you.
A table is a unsorted bucket of data.

In Access you should use a form to display and edit data and you can use the control wizards to create a button to copy a record and then let you edit it.

 

[plog]

Let's say you have this data:

ID, FName, LName, DOB
1, John, Smith, 1/1/1990
2, Sally, Jones, 2/2/1981
3, Pete, Davis, 3/3/1992
4, Ron, Bush, 4/4/1983

As typed it the "middle" is between ID 2 & 3. But if you open it and its sorted by:

FName, the "middle" is between John (ID=1) & Pete (ID=3)
LName, the "middle" is between Davis (ID=3) & Jones (ID=2)
DOB, the "middle" is between 4/4/1983 (ID=4) & 1/1/1990 (ID=1)

A table never has a middle, because a table is just a big bucket you throw data into. Order exists in it only when you explicitly define that order via query with an ORDER BY clause. So perhaps you can explain your situation better and what you actually hope to accomplish. What will "inserting into the middle of the table" help you actually achieve?

 

[quest]

in my table id is hidden and no important to me. i need to insert record because that order will be impossible to get with query. so i select record where i want new record, select rest of records copy them, select again one record lower and to end of table paste and I have two same records in the middle, one for editing to be new record.

 

[GPGeorge]

in my table id is hidden and no important to me. i need to insert record because that order will be impossible to get with query. so i select record where i want new record, select rest of records copy them, select again one record lower and to end of table paste and I have two same records in the middle, one for editing to be new record.

This is impossible because, as others have stated, there is NO intrinsic sort order in tables, and hence no record can ever be "in the middle" without applying a specific sort order.

Somehow the records are already being sorted; we just don't know how that is being done. Apparently you are relying on that hidden sort though. Unfortunately that sort order is coincidental, not deliberate.

The actual process described is not that complicated anyway. You want to duplicate a single record and edit the duplicate?

Filter the records to select only the one record you want to copy. That leaves one record visible in the datasheet. Go to the row below it and copy the fields. Then you can edit the fields to be changed.

On a related and important point, all of this comes about because you are, it appears, editing records directly in the table in datasheet view. That's generally not a good idea. And the difficulties you have here are one of the unfortunate consequences of doing so. I recommend you implement record management in a form specifically designed for that purpose instead.

 

[plog]

i need to insert record because that order will be impossible to get with query

Again, a table has no order. You cannot rely on the way you insert data to remain that way in a table. This is not Excel. Access is a database and it operates very logically along a very logical set of rules.

If you need to sort your data in a way that you can not logically do so currently, then you need to add a field so that you can. You would add a numerical field called [SortOrder] and then you could manually type in numbers to control the way your data sorts. Then when you needed to add a record between [SortOrder]=7 and [SortOrder]=8 you could add a record with [SortOrder]=7.5

Again, though, it would be helpful if you gave us more insight than this misguided task you are trying to achieve. What is your database for? What does this data actually represent? What does inserting data illogically between records help you accomplish?

 

[The_Doc_Man]

i need to insert record because that order will be impossible to get with query.

This means the table is wrongly structured. The tail is wagging the dog here. You build the table to support the actions you need it to allow. If you want a particular sort order you build a table that allows you to perform the sort you want with a query. In an Access table where insertions occur, you will not even get the same record order twice in a row except by accident. You can't do what you want because you can't rely on table order to be stable or consistent. PERIOD.

Put a field in the table to support the sort order you want. Then use a query to present the table's contents in the sorted order, something you CANNOT do with a table.

 

[theDBguy]

in my table id is hidden and no important to me. i need to insert record because that order will be impossible to get with query. so i select record where i want new record, select rest of records copy them, select again one record lower and to end of table paste and I have two same records in the middle, one for editing to be new record.

This may or may not help, but take a look at this demo. It's not a complete solution for you, but it might give you some ideas anyway. Good luck!

Rearrange Record Sort Order

 

[Pat Hartman]

i need to insert record because that order will be impossible to get with query. s

There is some kind of disconnect but I can't put my finger on your problem. Why does every new record start as a copy of the previous record?

This logic makes no sense in the world of a relational database. It makes little sense if you're working with Excel. If you want to always review the most recently entered record, you can order the recordset descending by the autonumber as long as the autonumber is assigned sequentially.

Try to explain your process in terms of business rules rather than on failing to be able to locate a specific record.

The internal workings of a relational database are very complicated. Your thought process is like a paper file where you write one record, move to the next line and write the next. This is similar in concept to how a sequential file works. Excel is very similar to a sequential file. The difference is you can sort it, work on it and sort it back which you can't do without opening and closing an actual sequential file like .txt or .csv.