Question about Update Queries

[fluid]

I'm still learning Access and I've seen a lot of references to "Update Queries" . I was just wondering what they are, how they work, and maybe some examples...

I have a hunch that it'll help me with the problem I'm having.

Thanks.

 

[CameronM]

As the name suggests, UPDATE queries, update existing records in your database. You can build them using the Access query builder - so in Access 2003 you follow these rough steps

1. create a new query
2. add to this query the table you want to update
3. from the query menu choose "Update Query"
4. you will notice that a new row is show in the query builder called 'Update to'
5. drag&drop the fields you want to update from the table
6. In the update field enter the new value
7. add any criteria you need to select the records you want to update

For example to update Field1, in TableOne for every record where the date is greater than today, your SQL update string would look like this:

UPDATE TableOne SET TableOne.Field1 = "NewValue"
WHERE TableOne.StartDate>Date();

There's a million more things you can do, including updating fields based on the results of another query, but I hope this gets you started.

 

[fluid]

Thanks so much for the info....

Specific question for ya...
Can you use the update query to update a new record, and only the new record.

 

[John Big Booty]

Certainly, however you will have to work out a way of identifying what is a new record and what is an old record.

You could add a check box (for example) to your table that gets checked or unchecked when the record becomes old. Then put the opposite in your update query as a criteria.

 

[CameronM]

If it is a new record, as in it has not been entered in the database before, you would use an INSERT query. If the record has just been entered and you want to change some of the details, you could attempt to get the primary key and then use an UPDATE query.

To refer to a specific record, you will always want to know the primary key that is unique to that record, then you are free to UPDATE as much as you like with a WHERE clause like "PrimaryKey = [TheNewRecordID]"