insert a record between two records

[amirhosein]

Hello

I have created a table of Some machines specifications.

Item Name Type location
1. Sieve156 Sieve 3rd floor
2. Rollermill1 Mill 1st floor
....
...
...

When inserting for example a new sieve machine I want database
add automatically this machine between items 1. and 2. and assign the item
number 3 to rollermill1. but this new record is added to the last row and its
item number will be 3 but it should be 2.
What shall I do for this problem? (in excel it is easily possible to add a row
between two rows.. is it also possible in access?)

thank you

 

[neileg]

A table is an unordered recordset. The position of a record in the table is not relevant unless you impose an order on it. That is quite different from a spreadsheet, as you suggest.

If the order is important, you will need to apply the order in a query or in a form, not in the table.

 

[ScottGem]

To add to what Neil has correctly said. An Item number essentially has no meaning in a database table unless its used to impose and order on the table. But trying to renumber items on the fly is a cumbersome and unecessary task.

What you need to do is to figure out a sort order for your items. If you sort by type then all the Sieves will be together and all the Mills will be together etc. If you sort by name, then Sieve156 will come before Sieve157 and after Sieve100. However, Sieve100 will come before Sieve20.

The point is you need to design your tables so you can create a logical sort order. That sort order is applied on report ansd/or forms.

 

[mga]

Can you help me

A table is an unordered recordset. The position of a record in the table is not relevant unless you impose an order on it. That is quite different from a spreadsheet, as you suggest.

If the order is important, you will need to apply the order in a query or in a form, not in the table.

===================================
Hello, Neil!
I have the same problem with inserting empty record between two records in non-indexed table. Table must be non-indexed like Excel work sheet.

If you know solution, please help me.

Regards,
Gennady

 

[richary]

It looks as if both mga and amirhosen have mis-understood neilg and scottgem.
Hope fully I can shed a little light

You need to sperate two issues - how access stores data and how you (or your users) actually view data. You care only about the latter. Leave the order of storage of data in tables to access.

You can use queries, forms and reports to impose the order that you wish to see the data. Don't rely on the table view to see the data. But, and this is a big but, you will need to think about how to code how you will view the data in queries/forms/reports. Will it be by date, alphabetical, numeric or some combination of the three? Or some other way?

Hopefully I haven't over-simplified too much and there is a lot more to learn than I can put into three paragraphs, but the seperation of the order in which data is stored from how it is viewed is an important one in relational db design .

 

[ScottGem]

===================================
Hello, Neil!
I have the same problem with inserting empty record between two records in non-indexed table. Table must be non-indexed like Excel work sheet.

If you know solution, please help me.

Regards,
Gennady

Richary gave you a good summary of what Neil and I said. But one thing I should add. There should be no such thing as a "non-indexed" table. Every table (in a relational model) needs some unique value to serve as a primary key. By definition a primary key is indexed. This is an issue of planning and design not Access. You need to design your tables so there is a logical sort order.

 

[The_Doc_Man]

The ONLY way to do this, and it stinks, is to link to an Excel spreadsheet as though it were a table. Insert your blanks into Excel. When dealing with the data from Access, filter it by query to eliminate records for which some key field is blank. And BE AWARE: You cannot update the table from Access.

The key to this whole problem is to recognize that Access and Excel have two totally different functions in an office environment.

Think EXCEL = accountant's paper analysis pad. Each line and column means exactly and only what you wanted it to mean when you wrote it.

Think ACCESS = well-stocked library's card file with cards listing the books by title, subject, author, publisher, LC number, supplier, and shelf number - if you have an anal-retentive librarian. Not at all the same.

These are mental models for examining the differences between Access and Excel. Don't design anything for either one until you understand both models well enough to see the differences. Otherwise you are doomed to curse the day you opened the Access documentation.