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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Al - 12 Apr 2006 14:25 GMT

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

Reply to this Message

Duncan Bachen - 12 Apr 2006 14:47 GMT

> 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
[quoted text clipped - 4 lines]
> Thanks,
> Al

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.

Signature

-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.

Reply to this Message

Al - 12 Apr 2006 14:54 GMT

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

Reply to this Message

Duncan Bachen - 13 Apr 2006 17:02 GMT

> 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

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.

Signature

-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.

Reply to this Message