How do I update: Macro, Update Query, or SQL

A

Al

I have a simple example. A simple single table db. My table has 20 or
so fields. I want to modify the Sub_Account field based on the Accout
field. Something like:
If Accout = 5005 then make Sub_Accout = 0005
How do I do that?

Thanks,
Al
 
D

Duncan Bachen

Al said:
I have a simple example. A simple single table db. My table has 20 or
so fields. I want to modify the Sub_Account field based on the Accout
field. Something like:
If Accout = 5005 then make Sub_Accout = 0005
How do I do that?

Thanks,
Al
Click to expand...

Is this a one time thing, or an ongoing thing?

You can accomplish it several ways, but the simplest is an update query
with the criteria of [Account] set to be equal to 5005, and the
[Sub_Account] Update to set to 0005.

You can run it, and it's done. If this is going to be an ongoing thing,
you'll either have to make the query based on a form (where you can
supply the new values for the Sub account and which account to look
for), or write the code using an SQL statement that does the same thing
as the query.
 
A

Al

Thanks for your response Duncan, but I don't understand it. Do I type
the criteria in exactly as you've written? That's going to test the
value of Account or set it?

Al
 
D

Duncan Bachen

Al said:
Thanks for your response Duncan, but I don't understand it. Do I type
the criteria in exactly as you've written? That's going to test the
value of Account or set it?

Al
Click to expand...

When you create your query initially, it's a SELECT query. That means it
only returns records, it doesn't change them.

On the QBE grid, you would add the Account field, and set it's criteria
to 5005.

Go ahead and run it. You should only get those fields whose Account = 5005.

Now, go up to Query->Update Query.

This will change it into such, and will add an additional line to your
QBE grid called "Update To".

Make sure that SubAccount is on your list of fields, and put 0005 in the
Update To grid.

Now when you run the query, it will return all Account=5000 and it will
SET all SubAccount = 005.
 
Top