Updating records through a query

[Pippa1]

Hi

I'm sure this should be really simple but I can't work it out.

I have a database where I need to send letters to people.

I run a query to search for the relevant people, export the list to excel and use a mail merge to send them the letters.

I then run another query where it runs the same search and shows the list of people in a table with a date field. I then need to put in the date that I sent the letters for all those people. I'd be quite happy just to write the date in and then copy that down all the fields in that column but that doesn't seem possible in ACCESS.


So I am sitting and entering it (or copying it) into each cell individually.

Is there a way to make it copy down or do I need to do something more sophisticated where ACCESS populates that field for me with a date I tell it?

I'd be very grateful for any advice.

Many thanks.

 

[jdraw]

You may get some ideas from this free youtube video by Steve Bishop.

Is it a standad letter to all recipients?

Good luck.

 

[Pippa1]

Thank you for responding. Yes it's a standard letter. I'm fine with the exporting part, it's just the updating the dates that I'd like to speed up.

 

[jdraw]

The video shows how he uses the Access table to populate various bookmarks.

 

[Mark_]

I run a query to search for the relevant people, export the list to excel and use a mail merge to send them the letters.

It sounds like you already have the criteria for doing an update query.

For myself, I would use this query to update the "Date send" PRIOR to doing the export, then only export those records that have a "Date Send" that matches the current date. This would allow you to review the list to be sent prior to export and print, hopefully catching anyone who should/should not be sent to in that batch.

 

[Pippa1]

Thanks Mark. It's probably my controlling nature but sometimes the letters are printed but not sent on the same day so I like the date sent field to be updated when they are actually sent.

Can I still use an update query in this case?

For both the queries I currently use the user chooses which records to bring up based on a different date field and as long as they enter the same date field in both queries it will bring up the same set of people.

 

[Mark_]

Were it me, I would do an update query when you export to fill in the date but have a form based on the same criteria to "review" the date sent. That way only for those records sent on a different date would you need to make a change.

 

[isladogs]

Pippa,

Did you know that you can do mail merge directly from Access without having to export to Excel. To make it really easy, use Albert Kallal's 'Super Easy Mail Merge' which has been around for several years and really is easy to use

http://www.kallal.ca/msaccess/msaccess.html

Website is dreadful but the code is very good

 

[Pat Hartman]

I don't like mail merges. I much prefer OLE automation. It gives a lot more control especially the abilities to load tables in a document so you can show both parent and child data easily which isn't possible with the mail merge option. Here's an example. The app and the letters are in separate documents due to size constraints on this forum for uploads.