update query

[datsun100a]

I want to update several record entrys in a access sql statement but as I am not an expert, I don't know the correct syntax. Basically I want to update a record then another then another etc within the same statement.. if possible. I have tried THEN or / to seperate the update statement but no luck.

 

[Taff07]

Are u trying to change every single record within a table or only certain ones dependent on criteria?

The sql below will change the value of field1 for every record in the table.

UPDATE

SET

.[field1] = "NewData";

You will need to expand this of course to include criteria.

Hope this helps

Ian

 

[datsun100a]

I am trying to change records in several tables. I have several tables for products as they could have the same serial number, when an item is used in an assembly I do not want to be able to select it again in another so I have a field for availability and want to set it to 'no' when it has been assigned to an assembly.

 

[Taff07]

So you want to run sql on every other table within the database changing any matching serial number from the inital table to a value of No. is this correct?

Is there any reason you can`t just do this within code and just write a seperate sql for each table using a variable to determine the specifc criteria before any of them begin executing?

 

[datsun100a]

Basically to record all items to make product X I made a form with all the items available. Those items are serialized and you can only use them once. Once you have selected all your items I want to make all the items which have been used to make product X unavailable, to do this the form selects from each item table those items which have a null value in the available field. The only way I can see to update this field is seperate update queries and execute them from a macro. My VBA experience is very shakey or I would have done it that way.

 

[Taff07]

You can try a union query maybe, but I`m not fully up to speed with them myself and not sure if they allow editting in them.