Deleting a record from a table

[hunterfan48]

I have a field called [transaction ID] in which I manually enter a new number for each unique record.

0001
0002
0003
0004

Just a simple question, if I delete 0003 then my 0004 will move up be right after 0002. There will no longer be a record containing 0003. Is this the best way to delete record 0003??? Or can I delete it so that 0004 will become 0003 and so forth?

thanks!
Brady

 

[mcalex]

You don't care what the transaction ID is. Therefore it doesn't matter if it is in order, in sequence or in hexadecimal.If you need a sequential, ordered field, you should add it as a column, but don't use an ID field for it.hthmcalex

 

[Galaxiom]

TransactionID is probably the primaryKey that links it to the other tables. You really don't want to change that.

 

[jdraw]

Do you really want to delete a transaction? Will you need that transaction data for audit/history purposes? You can add a field to a transaction table to indicate its status (Active/Deleted), and keep the record, but use the status field to include/exclude that record from calculations.

Just my $.02

jdraw

 

[hunterfan48]

thanks for all your input...

To clarify, yes I do want to delete a few (records) transactions because I have properly accounted for them in another column. For example, I was creating a new record to list the shipping costs for a previous transaction. Well, I created a new field ([shipping costs]) n that I can indicate for each specific transaction what my shipping costs were.

For example,
Trans ID Date Description Price Shipping Fees Shipping Costs
0001 8/15/11 2 Card Bowman Lot $3.50 $2.00 $0.50 $1.50

As opposed to then creating a new record as such to mark shipping costs...

0002 8/15/11 Shipping Fees for Transaction 0001 $1.50

Makes more sense doesn't it to the way I changed it right? So then now I have to delete some of these extra shipping costs records because I have moved them up to that specific transaction and to keep both of them now would be to count them twice (don't want to do that)

Please advise further...thank you

 

[hunterfan48]

Where's Bob Larson when you need him? LOL

 

[Galaxiom]

It is easily done but you are worrying about something that doesn't really matter. Databases don't care if some numbers are missed.

However if you really do want to renumber then simply copy the table structure to a new table and add an extra autonumber field. Append the old records to the new table and they will be renumbered in the new field.

Then for any linked tables, update their TranNumber (foreign key) to the new autonumber field value through a join on their original relationship.

Delete the old TranNumber field from the main table and rename the new one.
Reestablish the relationship.

 

[Brianwarnock]

If I read this correctly you should be able to find the original transaction ids of the records updated as their new field will be not null or zero whichever the default is. Having got these ids in a query you can then match them to the records where right(Description,4) =Id in the query.

That doesn't read very well hope you understand it but I am working on my wife 's IPad!! And cannot figure out all the options!

Brian

 

[hunterfan48]

It is easily done but you are worrying about something that doesn't really matter. Databases don't care if some numbers are missed.

However if you really do want to renumber then simply copy the table structure to a new table and add an extra autonumber field. Append the old records to the new table and they will be renumbered in the new field.

Then for any linked tables, update their TranNumber (foreign key) to the new autonumber field value through a join on their original relationship.

Delete the old TranNumber field from the main table and rename the new one.
Reestablish the relationship.

So simply, it will do no harm to delete the record and I don't need to worry about it further correct?? I've already accounted for this record in a newly added field for this same table so my numbers are still correct.

The transaction ID is just my primary key to uniquely define each record. the data type is (number) and I have an auto format for putting a couple zero's in front (preference to make it stand out...nothing more)

Like I said, it is not autonumber so it does not enter the next number in sequence. I do that on my own...if I'm entering a new transaction ID for the next record, why not just use the next number in line?? So I do...

But when it comes to a situation like this now where I've double accounted for some amounts and I actually need to remove a record, is it a bad thing that the records go sumthing like this.

0001
0002
0004

See, 0003 was there but now it's gone cause it was deleted. To me, I really can't see why it would matter to move all the other records up one transaction ID. Instead, every record will keep it's same transaction ID there just will no longer be a 0003, or a 0061, etc. etc.

Can you guys think of a reason why it would matter to keep an ordered sequence?? All it is just a field to uniquely define each record which I enter manually so I put it in order

 

[Galaxiom]

Deleteing a record would only matter if you had related records in other tables. Deleting the main record would leave orphaned records.

Ordered sequences don't matter to the database. In fact one type of autonumbered primary key uses a random number rather than a sequence.

Financial auditors do like to see an unbroken sequence of invoice numbers but these are often allocated separately from the primary key.

 

[hunterfan48]

Very well explained...thank you!