Delete First Record in a Table?

[Buckster]

I've got a situation where I need to append data to an empty table that will autonum the records for me, delete the first record of that table, run a series of queries using the table's data without that first record, then delete all the records from that table again to set it up for the next time that event needs to fire.

I'm having trouble figuring out how to delete just the first record from that table automatically as part of the sequence of events.

Any help is surely appreciated!

 

[Galaxiom]

Use a query to determine the record you want to exclude. This can be done with a Min() function on the field that indicates the age of the records. Don't use First() as it won't do what you expect. It can also be done with a DMin function.

Personally I wouldn't trust the autonumber to indicate that record because it can go wrong. Use a date/time or defined sequence number.

Rather than deleting the first record I would exclude it in the queries then delete them all afterwards. Otherwise if you process is interrupted and restarted you might end up deleting the "first" record twice.

 

[Buckster]

Use a query to determine the record you want to exclude. This can be done with a Min() function on the field that indicates the age of the records. Don't use First() as it won't do what you expect. It can also be done with a DMin function.

Personally I wouldn't trust the autonumber to indicate that record because it can go wrong. Use a date/time or defined sequence number.

Rather than deleting the first record I would exclude it in the queries then delete them all afterwards. Otherwise if you process is interrupted and restarted you might end up deleting the "first" record twice.

That's a really good point, and I'll have to think about how I might better pull this off to ensure the end users get the right info on the output. At the moment, I can't think of a common thing that would help me exclude that single record with a query each time the end user runs it because I have no way to predict what data it will contain, but maybe I can come up with something to help identify it.

Much appreciated!

 

[Galaxiom]

Could you add a field for the Date/Time of entry or do you just get given the data in a batch? It is quite easy to find the oldests record and exlcude that in the query.

 

[Buckster]

Could you add a field for the Date/Time of entry or do you just get given the data in a batch? It is quite easy to find the oldests record and exlcude that in the query.

I actually have that data and could use it, but the "first" record in the dataset that's generated is "first" by other criteria than that, so that date method would choose the wrong record to delete if I included and used it.

Maybe if I do a better job of explaining, it will help you folks help me.

Attached is a sample of data in an Excel sheet, zipped up, that might help with the explanation. It's the first 19 records of more than 18,000 in the table that was appended into the empty temp table as follows:

The query that gets run to put together the dataset for the temp table above sorts the data in a particular manner, with the [PoleNumber] grouped and then sorted as you see (It is "1" only in this example - In fact it will almost never be the same set of [PoleNumber] data twice from run to run).

It also sorts those groups of [PoleNumber] by [PoleCalcPr] so that the lowest [PoleCalcPr] number is at the top of each [PoleNumber] group. That number will always be a zero and the [Device Type] will always be a 36, but there will be hundreds or even thousands of those in the table, 1 for each group of [PoleNumber]. I need to keep all of them except that very first, top one, and I need to keep (or end up with) the sort order for the table as is.

It then appends that sorted recordset into the temp table, which has an autonumber [ID] for reference and to provide a final sort order to the table itself. (While I'm at it, I'd like to re-set that to 1 each time this thing runs).

Now that I've got my TempPoleMeas02 table populated with the data, I need to delete the very first record from it, [ID] 18870 in this case. If I can figure out a way to just not include it in the dataset in the query that appends the table, that's fine too, as long as the end result is the same - all the data, except that one single record.

Any ideas on how to accomplish this are greatly appreciated!

 

[Galaxiom]

Records in a table do not have an intrinsic order. They will usually start out appearing in the order of entry but at any time they may be completely jumbled.

If sequence matters it is essential that some field is able to be ordered.

Generally if you start fresh with an autonumber each time and it is a single append it will be consistent.

Sometimes though automumber will launch off somewhere unexpected. In my experience this happens after using a query to insert records with autonumber values lower than the last record in the table. Most times it is fine but occasionally for unknown reasons it goes wrong.

Will have a look at the sample tomorrow.

 

[Buckster]

Records in a table do not have an intrinsic order. They will usually start out appearing in the order of entry but at any time they may be completely jumbled.

If sequence matters it is essential that some field is able to be ordered.

Generally if you start fresh with an autonumber each time and it is a single append it will be consistent.

Sometimes though automumber will launch off somewhere unexpected. In my experience this happens after using a query to insert records with autonumber values lower than the last record in the table. Most times it is fine but occasionally for unknown reasons it goes wrong.

Will have a look at the sample tomorrow.

Thanks so much!

I think I may have just hit on a way to exclude the first record in the set during the query. I made a separate query with a single field to just identify the lowest [PoleNumber] with a Min, then used that query as part of the append query to exclude that [PoleNumber] IF it also has a [Device Type] of 36.

That leaves me with the possibility of a bad sort, per your info, and a need to ensure that I don't get one. Other than AutoNumber, is there a way to sequentially number a set of data as sorted that is reliable?

 

[Buckster]

Okay! I got this!

Once I realized that it doesn't necessarily have to be sequentially numbered in the table because I can just resort as needed on the queries that pull from the table in the next steps, I gave myself a whack for over-thinking it, and hit the gas to work up the queries that follow and set up the sorts! Works perfectly!

Thanks so much, especially for helping get my brain in gear!