Hi,
  Is there a way to insert a row into the middle of a table?
The users may need to insert new records into my table and in order to keep the records in order I need to be able to put the record in the middle of the table instead of at the bottom of it. Each row has a unique number [sort key], which I use to order the rows. A new record would have to take over another records sort key, so each record after the new one would have to move up by one.
Oh, and I have a form that the user will use to insert the values of the new record, they won't directly use the table.

Thanks for any help

Dave

Dave,

It really shouldnt matter where your new line is in the table, your ordering should be taken care of by a query (or rowsource, whatever) when you display it. Herding cats is a phrase that springs to mind.

Your record should have a Primary ID, this does not need to be involved in your sort. Your sort criteria can be pretty much anything, but I would worry about having to run an update after a new line just to sort. Can this not be resolved with a date field, and/or other data fields combination?

Question: You are doing a sort when you show your data (ie: a report or form), can the sort order be established by the data alone, without using your [sort key] when the report/form is opened? This is going somewhere...

Lee

Thanks for the reply Lee!

This table is populated, to start with, by importing data into a seperate table. It starts out with a unique number for each record. The sort key is added later for a couple of different reasons. Each record is a set of product. This product is being assigned to different locations in bins. The only date is the date they are to be shipped out and it doesn't matter if they are in order by date. The sort key is the only unique identifying field, as the unique number that gets importing in to start with is not really helpful at all. Its not in the correct sequence.

"Its not in the correct sequence"... Why not? and where? In your report, form or the table?

If you mean in the table, then don't worry.
If you mean the form or report, then put the sort criteria in your source query.

If the sort key is established from other fields in your table, then simply calculate the sort key as part of the data preparation (source query), dont do it before though.

The imported table is not in the correct sequence. It has a field that contains unique numbers, but I think it just shows the order that they were put in the table. I have to adjust the sequence in which the records fall, so the unique number gets scrambled around and is useless to me. After, I have the records in the correct sequence then I add another field that contains the sort key. I use the sort key to "flip" the table; it is sorted in descending order.
At this point is where the user sees it. If they need to add a record, they are prompted to enter the sort key where the new record should fall. When a new record is added there is a field that is set to Y, so I use a select query to sort by the sort key desc and by the modified field "Y". This query works, but I want it to sort the table and not just in the query.

Sorry, not sure I can help you, since I dont really see or understand your problem. So, I may be making things worse?!

All I can say to help you is: this isnt Excel. It really doesnt matter that you have data jumbled up in a table, that is the point of the database front-end... to straighten out your jumbled up.

"After, I have the records in the correct sequence then I add another field that contains the sort key" - dont do this, dont add another field to your table... Query the data and calculate the sort key in the query, it should be logic driven - even with user-input fields. Then when and if the data changes, simply refresh the query, ie: re-calculate the sort key.

If there is user-input data involved in the [sort key] then you will have to add some sort of date (ie: most recent addition or similar) in order to let your logic decide what happens.

But seriously, if your query does work and it is just the table you are worried about... don't be. Tables are just places to put your data, they are supposed to be jumbled up!

leehambly is correct. You are misunderstanding the use of a table, if by table you mean a table in a relational database such as SqlServer, Oracle, MySql, or Access. Records in a table are an unordered set. The position of records in a table has no meaning to the database, only to your code and use of the data. In other words - you have to order it yourself based on indexes.

Again: There is NO concept in the database of the order of the records in a table. You can apply order to records you retrieve by ordering the records with your select statement, and in some databases you can describe indexes to allow for quicker retrieval when appropriate. You have to get an understanding of this reality if you wish to implement the functionality you desire. To take this even further - just because you put records into the table in a certain order, IT IS NOT POSSIBLE TO GUARANTEE that the database will store and present those records in the order you inserted them. This is a defining rule of relational databases. The database itself is allowed to store the records in any order it deems is efficient for it to do so.

You can NEVER "sort a table" as you say - you CAN sort the results of a query.

Woody Z http://www.learntoprogramnow.com

OK. This sheds some knew light on the matter, I think. I might have to take a different look at my approach.

Thanks for the Input

-Dave

Thanks again for the info guys!
It made my brain hurt a little, but I think I understand now. I was misunderstanding the concept.

You are welcome. These things are a bit puzzling because of the way we visualize data. Of course, how data is conceptually stored (as rows in tables) and how it is actually stored (in rdbms we are not supposed to know or care) are two different things. The important thing is that if we store our data properly, we can retrieve it when needed and in the way we want to work with it.

Woody Z
http://www.learntoprogramnow.com