automatically generating letters based on a dated field

[russm101]

Does Access have the ability to automatically generate letters based on a dated field. If so, How?

 

[BoxHead]

Yes. There are a number of ways to do this depending upon the data you have, how you store it, how you query that data and how you track what letters need to be sent and which letters have already been sent.

If you could provide more information, I or someone else here could probably help with that.

John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

Thanks for the quick response!

I have a simple customer database table listing address contact information. The forms have textbox fields with dated content. I need to reference the dated field and produce a letter (birthday, holiday, anniversary etc.) that can be mailed to the client.

Thanks....Russ

 

[BoxHead]

Russ,

First you have to decide how far in advance you want the letters generated. Let's say 10 days.

I assume you have at least 2 tables: 1 'customer' table and 1 'occasion' table.

Build a query with the customer address table joined to the occasions table on the customer ID field. I imagine that the occasions table has four fields? CustID, ocDate, ocOccasion, ocSentDate.

Add the ocDate, ocSentDate and ocOccasion fields to the query and in the criteria for the ocDate, enter <=DateAdd("d",10,Date()). In the criteria for the ocSentDate enter Is Null.

This should return any special occasions falling on the tenth day from today or before where a letter has not already been sent.

That should give you the data you need.

For the letter, you can create an access report or you can create a Word document and use Mail Merge with the query results. I prefer the Word document because it is easier to edit.

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

Great!

Is there a way of automating the word document generation from the results of the query or does that part of the process need to be manual.

Thank You very much !

 

[BoxHead]

I believe that when you mail-merge the word document with the query results it will automatically contain the data when you open the document. If the Access DB is closed, it may give you a message box telling you that it is importing the data.

The Mail-merge help and menus will be able to tell you more on that than I can. You should be able to print your address labels or envelopes using Mail Merge as well.

First, create and save your query and then from a new Word document, select Tools>Mail Merge (or Tools>Letters & Labels>Mail Merge depending on version). There are about six steps it will walk you through to get the basic document linked and started. You can dress it up for the occasion as you wish once you get the data issues clean.



HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

I followed the query and table setup instructions. I linked the two tables. I had to create the occasion table with the sugested field. The dataview is coming up blank, I must be missing a valuable piece of information. I know how to link tables, but need clarity in how they function after being linked. Do I need to add additional fields to the occasion query?

Thanks...Russ

 

[BoxHead]

Could you post the table names and the fields they each contain?

Like:

tblTable1
custId
custName
custAddr1

That would make it a lot easier to see where we're not connecting.

Thanks,

John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

Hi These are the tables and fields.

Table: Agent Pipeline
Fields:
ID = Keyid(autonumber)
SR = Agent Name
FirstName = Client data
LastName = Client data
StreetName = Client data
City = Client Data
State = Client data
Zip = Client Data
CreditPullDate = Date reference
DateClosed = Data reference

I am trying to produce letters and/or contact popups that will appear 7 days after the CreditPullDate and the DateClosed. I also need to reference which SR is the agent for this contact.

Thank You, I really appreciate your help with this task.

Russ

 

[BoxHead]

Russ,

I thought there was a second table?

John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

The second table is the table you recomended me to create. It is the Occasion table.

Table: Occasion
Fields:
ocDate
ocSentDate
ocOccasion

Thanks a million......Russ

 

[BoxHead]

We seem to be missing an ID field for the agents and/or the Clients. Are they one in the same?

It looks to me like the AgentPipeine table could have multiple records (Credit Pull Dates) for the same agent and/or the same clients. Is that right?

I'm afraid I'm not quite getting the structure here, so let me put together a sample that would accomplish what you asked for and then you'll be able to see which fields need to be linked.

Bottom line; if we have a record in tblOccasion with values;

ocDate: 10/10/1980
ocSentDate: 9/30/2005
ocOccasion: Birthday

we have to have a field that tells us whose birthday it is.

The 'Agent Pipeline' table with the Customer/Client/Agent information would either have to contain all of the occasion information or the 'occasion' table has to have a field that tells us whose record to look at in the Agent Pipeline table.

Let me see what I can come up with and I'll post back later.




John

Use what you have,
Learn what you can,
Create what you need.

 

[russm101]

The AgentPipeline table does have all of the reference date data needed. I can add the Occasion type field if needed. I only created the Ocassion table because I miss understood what you were asking. I only use one big table(AgentPipeline). It has all agent and client data in it.

I apologize for the confusion. The SR field represents thge agent. The client name is seperated in two fields FirstName and LastName. The date field that I would want to reference is the CreditPullDate.

Thanks for your patience...I've learned alot about programming in Access from patience helpful people like you. I've only been coding in Access for about 5-6 months

 

[BoxHead]

Russ,

I'm still not undersatnding how you plan to send letters for the occasions you asked about when you don't seem to be storing that information.

The question you're asking seems to be changing.

FROM October 6th:
I need to reference the dated field and produce a letter (birthday, holiday, anniversary etc.) that can be mailed to the client.

FROM October 7th:
I am trying to produce letters and/or contact popups that will appear 7 days after the CreditPullDate and the DateClosed.
and...
The date field that I would want to reference is the CreditPullDate.

The following table should give you the results you want as far as getting all records where the CreditPullDate or ClosedDate are seven days ago.

SELECT tblAgentPipeline.Id, tblAgentPipeline.SR, [FirstName] & " " & [lastname] AS ClientName, tblAgentPipeline.StreetName, tblAgentPipeline.CityName, tblAgentPipeline.StateName, tblAgentPipeline.ZipCode, tblAgentPipeline.CreditPullDate, tblAgentPipeline.DateClosed
FROM tblAgentPipeline
WHERE (((tblAgentPipeline.CreditPullDate)=Date()-7)) OR (((tblAgentPipeline.DateClosed)=Date()-7));

There are still questions about what you're actually after here, but to create any sort of letter, you either have to use Mail Merge in Wrd as I explained above or create a report based on this query where you have text fields that would have control sources along the lines of...


= "Dear " & [ClientName] & "," & Chr(13) & Chr(10) & "Looking at the calendar, I realize that you have a special day coming up on " & [DateClosed] & " and I didn't want this special occasion to pass without stopping tp say..."

I think that using Word makes more sense because it allows you to edit more freely, but the choice is yours.

Play around with it and see what you can come up with.

HTH




John

Use what you have,
Learn what you can,
Create what you need.