Complex Delete replace command or.....

[danbo]

I need to be able to replace data that will be deleted if a customer is deleted. for example



ID CustomerName CustomerPhone CustomerEquipment
28 John Doe (530) 555-1212 RST3-2-11-4

When the customer line is deleted it takes everything with it. Is there a way to make it so that the CustomerEquipment data is still saved or transfered to another table where it can be seen?

Any help would be appreciated. Thanks.

[June7]

Why delete customer? Why would you retain CustomerEquipment if Customer is deleted?

Before the delete event could run an SQL INSERT action to save info to another table.

[danbo]

Why delete customer? Why would you retain CustomerEquipment if Customer is deleted?

Before the delete event could run an SQL INSERT action to save info to another table.

That's a good question and I should have addressed it in the beginning.
My database maintains records based on the customer ID. When a customer is no longer with us there record gets deleted. Along with that goes the customerequipment field data.

The customerequipment referes to facilities at my office. (RST3-02-11-04) would indicated that they are connected to equipment located at RST3, Shelf 2, Card 11, port 4.

Once they are no longer a customer this equipment is available for use and I would like to be able to list Available equipment in a query calling for all RST not currently being used by a customer. If this value is deleted along with the customer I have no way of knowing that it exists.

Im trying my luck at a "append query"
I have one table that lists all of the equipment and another that lists equipment currently being used, but haven't figured out how to say only append records that don't currently match a specific equipment number, RST3-02-11-04 for example.

[danbo]

Why delete customer? Why would you retain CustomerEquipment if Customer is deleted?

Before the delete event could run an SQL INSERT action to save info to another table.

Actually, the append query isn't going to work anyway. Problem....
ID Name Address Cable Pair Equipment Number
234 BOB 123 main st 234 RST3-02-04-06

The only thing I can actually get rid of is the customer ID. Everything else I need to maintain.

Boy, I tell ya what.... this whole database creation thing realy makes you think ahead. Too bad I haven't caught on yet.

That being said.... Is there a way to delete the record but maintain "MOST" of the data in a new row?

[June7]

Wouldn't the name and address info also go with the customer ID, leaving only the EquipmentNumber?

Problem is your data structure is not normalized. Review this http://forums.aspfree.com/microsoft-...es-208217.html

It is seldom a good idea to delete records. What if the customer comes back? Wouldn't you want that history?

You should have a table for equipment and show in this table where the equipment is checked out to. A field with customerID would do. If you want history of where equipment has been, have a junction table.
tblCustomerEquipment
EquipmentID
CustomerID
DateAssigned
DateReturned

Eliminates need to delete customer or copy equipment between tables.

There are probably existing template databases that fit your needs. The Lending Library is one that might be adapted.

[danbo]

Problem is your data structure is not normalized.

It is seldom a good idea to delete records. What if the customer comes back? Wouldn't you want that history? Better is to have a Yes/No field to indicated status (active or inactive). Use that field in queries to exclude the inactive records. You can make a returning customer active by simply changing this value. Much easier than the delete and copy data you are trying.

You should have a table for equipment and show in this table where the equipment is checked out to. A field with customerID would do. If you want history of where equipment has been, have a junction table.
tblCustomerEquipment
EquipmentID
CustomerID
DateAssigned
DateReturned

Eliminates need to delete customer or copy equipment between tables.

There are probably existing template databases that fit your needs. The Lending Library is one that might be adapted.

I agree with you on deleting records and was just looking to see what other avenues I could explore. I'm sure it's not normal, lol this is my first database and I have no clue what I'm doing, but..... it "seems" to be working except for this one last glitch. I guess the best thing I can do at this point would be to manually go into the database records, copy that equipment and paste it into a new record. and delete the equipment from the customers current record. Seems like a lot to have to do though.

thanks

[June7]

Best thing is to properly design the data structure. Look at my other post again, did some edits as you were posting yours.