Updating 1 field in all filtered records simultaneously.

[Riaang]

Help ,please ray:

Is it possible to update 1 field in all filtered records simultaneously with the same information. Eg, my table consist of 6000 entries but 500 of them must get a letter. Now I need to put the same letterdate on all these records.

Currently I filter the database and copy the date down 1 by 1.

Is there a faster easier way.

Regards
Riaan

 

[dk]

Hi,

You should be able to use an update query. Create a new query using Design View. When there, click Query, Update Query. You can then select which fields you wish to update, add any criteria and enter the value/expression you wish to use.

For more info, see the Create an Update Query topic in Access help.

 

[passinthru]

A follow on, if I may:

How does the update query behave when there is data already in the table being updated? Does it overwrite existing data? For example, I have a spreadsheet with dates that I want to import to a database. (There is an index and common field names and I've done this already so it's not a question of HOW to do it.) If I have dates in the spreadsheet which are different from the corresponding date in Access, will the update query overwrite the Access date with the Excel date? Or will it make no change?

If there is a date in Access but not in Excel, will the Access date be zeroed out? OR will it keep the existing date?

TIA.

 

[SydneyGeek]

When you run an update query it overwrites everything in the selected field(s) and records. If you don't want to overwrite existing data, set the update query to filter for NULL in the date field. That way you get to keep the dates that you imported from Excel.

 

[Riaang]

Thanks a lot guys.

 

[passinthru]

Let me see if I understand this: For simplicity's sake, we'll make the Access table and spreadsheet identical. There's the Index field, and one date field. (Index, Date)


Suppose Date has SOME dates filled in and I want to update it from the spreadsheet. Some of the dates that exist in Access are different from the dates that are now in Excel. Some dates that exist in Access are now EMPTY (no date) in Excel. Some new dates are in Excel that are not in Access. As I understand your answer, the Excel data will overwrite EVERYTHING in Access, so as long as the Excel spreadsheet always has the most recent and accurate data, life is simple. A simple update query does the job.

BUT! What if there might be some empty dates in Excel where Access has a date? I want to keep the Access date in that case. How do I craft the update query to handle this? In newbiespeak, please!