I've developed an Access DB that is used to manage line ads for a small newsletter publication. The ads are entered into the DB and at the end of each week, they must be retreived from the DB and flowed into a page layout program to go to print. Herein lies the reason for this posting.

The ads are composed of two segments - the "HEAD" and the "BODY". The head portion of the ad is bold and the body portion of the ad is regular. Each ad is given an AutoNumber ID and placed into one of 73 categories, such as Autos or Employment. These categories each have a graphic header that sits atop the entire category. (The ads then flow underneath these headers) Because not all ads will run constantly, there is a field that tracks the cancellation date of the ad. I have a query setup that populates a new table:

[CancellationDate] >= Date()

This way, I get all ads that have a cancellation date greater than or equal to today's date. Makes enough sense, right? At this point then, I need to "get" the ads out of the DB (export I suppose) and into the page layout program. My current system works, but it takes forever and I would like to find a more efficient way to handle this.

I run the Make Table query to populate a new table with only the ads that have not yet cancelled. I open Word 2000 and setup a Mail Merge catalog that is based on the populated table within Access. My mail merge within the document looks like this:

<<Head>> - <<Body>>

This will guarantee that the ads all follow the desired format of Bold Head, Space, Hyphen, Space, Regular Body. I then Filter the records by the Category Number (1-73) and run the Mail Merge. A new Word document is populated by any ads within the filtered category that have not yet been cancelled. I copy the ads from the Word document and paste them into my layout program underneath the appropriate header graphic. I repeat these steps of filtering, copying, and pasting until I have reached Category 73.

Like I said, this process works, but it is extremely tedious and bothersome. If anyone out there knows of any other way to export the ads from Access and bring them into my layout program, please let me know. It would be great to be able to placed a "Header Link" at the top of each category and just export all of the ads at once. That way, I would only have to copy and paste once and then just replace the text headers with the graphic ones. Any help would be appreciated. Thanks for taking the time to read this.