Creating a Delete Query

[lovett10]

I am trying to create a delete query that will only delete certain fields and leave others blank i tried to create the query itself but when i run it deletes all the fields when i only want to delete 12 of 15

Thanks for any help in advanced

 

[mdlueck]

Delete Queries remove Selected records, not only blank out certain fields.

Update Queries are able to blank out certain fields while leaving the record in the table.

Which do you really want to do?

 

[lovett10]

Delete Queries remove Selected records, not only blank out certain fields.

Update Queries are able to blank out certain fields while leaving the record in the table.

Which do you really want to do?

I want to delete some of the fields and keep some of them. The reason is, this is for maintenance for an engineer there are a list of sensors for example and he will enter all the required information e.g. current value, however each time he goes for the maintenance i want the fields that he changes to be deleted so he can enter them again from fresh. However as the sensors dont change they dont want to be deleted

Thanks for the quick reply

 

[mdlueck]

I want to delete some of the fields and keep some of them.

Then you will use an UPDATE query, specify the correct WHERE clause to only change the record(s) you mean to change, and only include names / values of cols you mean to change.

I posted here the correct VBA syntax to drive an Access UPDATE SQL using an ADO.Command object:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

That should be a good example for you to model after.

Perhaps practice via a GUI query doing the update, then take that SQL and wrap it into the ADO object to have it programmatically run.

 

[lovett10]

I essentially want to delete the contents of 12 of 15 of the columns in the table (not including the field name ofcourse)

is update really what i want?

thanks again

 

[mdlueck]

I essentially want to delete the contents of 12 of 15 of the columns in the table (not including the field name ofcourse)

is update really what i want?

As long as you do not want to get rid of the record ENTIRELY (leave those remaining fields), then Yes you must do an UPDATE to accomplish that.

DELETE would nuke the entire record / row.

Again, you do not need to specify columns you do not need to change the value of.

 

[Pat Hartman]

To build the update query, open the query designer. Create a select query that selects the columns you want to update. Then change the query type to Update. And finally put Null in the update to cell for each column.

Save the query and give it a nice name - qClearSensorValues

And - start learning the lingo. Delete means something very specific - and this is not a delete it is an update.

I must add that I question this process. It is highly unusual to keep updating the same record over and over again. You would never be able to report on changes.

 

[Addyman]

I must add that I question this process. It is highly unusual to keep updating the same record over and over again. You would never be able to report on changes.

This was my thought as well.

For something like this, I would have a table set up holding the details of the Sensor (SensorID as PK) and then a second table set up which holds Sensor Value and a Date / Time stamp (Sensor ID as FK, one to many relationship from your Sensor table).

Then, for each sensor, you would be able to query all past results, in date / time order and you would also be able to display just the latest value etc. etc. as required.

As this is to do with maintenance, you really should maintain a history.

 

[lovett10]

Thanks guys it works now

And Pat in the code before it wipes it clean it archives them so they can refer back if needed

 

[Pat Hartman]

You are going to a lot of trouble for something Access does naturally with bound forms.