Can I do this in Access? Please help

[caseysmydog]

The basic idea is that I have to send letters 5 times a year at 1, 3, 6, 9,12
months from Date of death of the person.
I need to set it up as follows:
Deceased Name Date of Death Caregiver's Name Address(City-State-Zip).
The letters are slightly different from each other(based on length of time
since death, ie, 1 month, 3 months, etc(I would need to store 5 letters).
This data base of names would be processed at the first of each month.
Therefore any one person would only get one letter that month. Some would get
none.
If the processing month is 1 month from date of death, I need the program to
print out a 1 month form letter using the caregiver's name and address, and
so on depending on the matching criteria.
Also, would need, if possible, to sort data by deceased's name which could
be done separately anytime.
PLEASE help, this is a vital need. Many Thanks.

 

[John Spencer]

You could use a query with a calculated field to identify the records.
For instance the one month letters would be based on

Field: DateAdd("m",1,[DateOfDeath)
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()), Month(Date())+1,0)

Another way to handle this would be

Field: DateDiff("M",[DateOfDeath],Date())
Criteria: 1

Replace 1 with 3, 6, 9 or 12 to get the addresses for the other letters.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[caseysmydog]

We are very new to Access. Where would the suggested query go?
Thanks
--
David

You could use a query with a calculated field to identify the records.
For instance the one month letters would be based on

Field: DateAdd("m",1,[DateOfDeath)
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()), Month(Date())+1,0)

Another way to handle this would be

Field: DateDiff("M",[DateOfDeath],Date())
Criteria: 1

Replace 1 with 3, 6, 9 or 12 to get the addresses for the other letters.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The basic idea is that I have to send letters 5 times a year at 1, 3, 6, 9,12
months from Date of death of the person.
I need to set it up as follows:
Deceased Name Date of Death Caregiver's Name Address(City-State-Zip).
The letters are slightly different from each other(based on length of time
since death, ie, 1 month, 3 months, etc(I would need to store 5 letters).
This data base of names would be processed at the first of each month.
Therefore any one person would only get one letter that month. Some would get
none.
If the processing month is 1 month from date of death, I need the program to
print out a 1 month form letter using the caregiver's name and address, and
so on depending on the matching criteria.
Also, would need, if possible, to sort data by deceased's name which could
be done separately anytime.
PLEASE help, this is a vital need. Many Thanks.

 

[Steve]

David,

If you need immediate help with this, I can create the database for you for
a very modest fee.

Steve
(e-mail address removed)

 

[John Spencer]

Create a new query
Select your table
Add the fields you want to see

In a blank field "cell" enter (replace DateOfDeath with the name of your field
DateAdd("m",1,[DateOfDeath)

Under that in the criteria cell enter
=1

RUn the query and you should get the data you want for the one month letter

Change the 1 to 3 to get the data for the 3-month letter

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[mcnewsxp]

ya got tables, queries, forms, macros, and modules.
so you have 5 guesses....;^)
you have a query designer that will help you assemble the data and include
your logic, but you might need to do some basic studying to understand the
concepts. it'll be time well spent.

We are very new to Access. Where would the suggested query go?
Thanks
--
David

You could use a query with a calculated field to identify the records.
For instance the one month letters would be based on

Field: DateAdd("m",1,[DateOfDeath)
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()), Month(Date())+1,0)

Another way to handle this would be

Field: DateDiff("M",[DateOfDeath],Date())
Criteria: 1

Replace 1 with 3, 6, 9 or 12 to get the addresses for the other letters.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The basic idea is that I have to send letters 5 times a year at 1, 3,
6, 9,12
months from Date of death of the person.
I need to set it up as follows:
Deceased Name Date of Death Caregiver's Name
Address(City-State-Zip).
The letters are slightly different from each other(based on length of
time
since death, ie, 1 month, 3 months, etc(I would need to store 5
letters).
This data base of names would be processed at the first of each month.
Therefore any one person would only get one letter that month. Some
would get
none.
If the processing month is 1 month from date of death, I need the
program to
print out a 1 month form letter using the caregiver's name and address,
and
so on depending on the matching criteria.
Also, would need, if possible, to sort data by deceased's name which
could
be done separately anytime.
PLEASE help, this is a vital need. Many Thanks.

 

[mcnewsxp]

what ever he quotes i'll do for half
;^P

David,

If you need immediate help with this, I can create the database for you
for a very modest fee.

Steve
(e-mail address removed)

 

[mcnewsxp]

sorry, just being an arse....

what ever he quotes i'll do for half
;^P

 

[John... Visio MVP]

David,

If you need immediate help with this, I can create the database for you
for a very modest fee.

Steve
(e-mail address removed)

As little stevie full well knows, these newgroups are provided by Microsoft
for FREE peer to peer support. Stevie is a well known troll who enjoys
preying on unsuspecting posters with offers of help of questionable value.

As he says, he will "create the database", but it is doubtful it will work
and do what you want.

John... Visio MVP

 

[caseysmydog]

Thanks, John...but tell you the truth, after struggling with this and not
being successful I'd be glad to pay! I sat up the table and ran the query
but got an error...said something about the fields not being compatible.
Question: after you run the query and get the data, how do you relate that
to the letter?

 

[John... Visio MVP]

There are many highly qualified people who will gladly help you for free.
Steve claims that he has helped thousands, but to date, none of his flock of
thousands have come to his defense.

The reason for maintaining a free peer to peer community is that the posters
can come here for answers with out being assault with offers to do the work.

So to get you on your feet, post the tables and the query you created. Once
you have the query working, you can feed the information into Mail Merge
with Word.

John...

 

[caseysmydog]

What you've suggested seems to be working...I will check more today and get
back with y'all...sure I'll need some additional help.

 

[mcnewsxp]

forgot to mention you also have reports. you can use your query as the data
source for your report - or do a mail merge. tho i've heard people say mail
merge is kind of unpredictable at times.

 

[caseysmydog]

Deceased Date of Death Caregivers Name Address
Beason, Alice 10/9/08 Santa Claus 9011 Horse Street, N Charleston, SC 29406
Smithson, John Henry 10/12/08 Jane Olivia Alexander 4628 Ashley View Avenue,
Charleston, SC 29405
Jones, Sam 10/26/08 Asia Jones 1999 Victory Rd, Summerville, SC 29847
Hickcock, Wild Bill 09/10/08 Calamity Jane Tombstone Rd, Deadwood, SD 67934
Earp, Wyatt 06/17/08 Doc Holliday 13 OK Correl Rd, Tombstone, AZ 63592
Rogers, Roy 03/22/08 Dale Evans 1214 Bullet Rd, Hollywood, CA 98645

Above is the Table known as Table1

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks
David

 

[John... Visio MVP]

Can you supply some more details on something you said earlier?
"got an error...said something about the fields not being compatible."

Can you also explain why you have a criteria of Date of Death = 1?

John...

 

[John W. Vinson]

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks

If you want to display a query here, please open it in design view; select
View... SQL from the menu; and post the SQL text here. It may look cryptic but
it's the *real* query, for which the grid is just a design tool, and the folks
who answer questions here can read it like today's newspaper.

As written - if I'm interpreting it correctly - this query will return an
error because the expression Dateadd("m",1)date of death)) makes no sense.
Even if it were corrected to Dateadd("m",1, [date of death]) it would return
only those records where the Date of Death was December 31, 1899 (one day
after the "anchor point" for Date/TIme fields), or was equal to one month
after the date of death - so you'll get no results.

Perhaps you could explain in words what results you want from the query.

 

[caseysmydog]

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,["date of death"])));

I would like to put the records, one per person, into the table as follows:
last, first, date of death, first name, last(caregiver's name), street
address, city, state, zip.
If the current date of processing is one month from date of death then move
that record to print and also move name(first-last caregiver, complete
address) to a stored letter and the name would be at top as any letter is.
THEN, run same file for current date 3 months after date of death, move to
print letter stored for 3 months, and so on, until all dates are tested, 1
month, 3 months, 6 months, 9 months, 12 months.
Not at all sure where to store these form letters (5 of them) and link up
with information (caregivers name, complete address.)
Need specific help here.

LETTER SAMPLE:

Sam Jones
4628 Ashley View Lane
Charleston, SC 29405

Dear Sam,


Many thanks
David

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks

If you want to display a query here, please open it in design view; select
View... SQL from the menu; and post the SQL text here. It may look cryptic but
it's the *real* query, for which the grid is just a design tool, and the folks
who answer questions here can read it like today's newspaper.

As written - if I'm interpreting it correctly - this query will return an
error because the expression Dateadd("m",1)date of death)) makes no sense.
Even if it were corrected to Dateadd("m",1, [date of death]) it would return
only those records where the Date of Death was December 31, 1899 (one day
after the "anchor point" for Date/TIme fields), or was equal to one month
after the date of death - so you'll get no results.

Perhaps you could explain in words what results you want from the query.

 

[Tony Toews [MVP]]

sorry, just being an arse....

<chuckle>

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John W. Vinson]

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,["date of death"])));

I would like to put the records, one per person, into the table as follows:
last, first, date of death, first name, last(caregiver's name), street
address, city, state, zip.
If the current date of processing is one month from date of death then move
that record to print and also move name(first-last caregiver, complete
address) to a stored letter and the name would be at top as any letter is.
THEN, run same file for current date 3 months after date of death, move to
print letter stored for 3 months, and so on, until all dates are tested, 1
month, 3 months, 6 months, 9 months, 12 months.
Not at all sure where to store these form letters (5 of them) and link up
with information (caregivers name, complete address.)
Need specific help here.

LETTER SAMPLE:

Sam Jones
4628 Ashley View Lane
Charleston, SC 29405

Dear Sam,

You're misunderstanding how tables relate to queries and to reports.

You don't need to put data into a table - "move to print" as you say - to run
a report on that data! Instead, you would have all of the data - date of
death, caregiver's name, address and so on - stored in a table; the data just
stays there. you would base your Report on a Query selecting which records
generate a letter based on the date, using criteria. You also don't need to do
any looping or "run the same file" iteratively - Queries return a set of
records, and a Report based on a query will print all those letters.

Where to store the letters? I don't know. I don't know the content of the
letters, or whether they are just text or have sophisticated Word formatting
or what. If they are just text, I'd keep the message body in a Memo field in
the table, and create a Report based on the query to print the letter. If you
are using Word features (even fairly simple ones such as italics or bold
text), you may need to take a more complex approach, storing five documents as
Word documents and using Automation or Word Merge documents to print them.

The data - names, addresses, dates, etc. - would be in a Table. The formatting
and appearance of the letter would NOT be in a table; it would instead either
be a Report with textboxes bound to the fields that you want to display,
arranged on a page to print a nice looking letter, or would be a Word
mail-merge document with bookmarks for the Access table fields.

 

[caseysmydog]

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])));

John, I replied to your post but it seems not to have posted.
Anyhow the above is the query based on earlier suggestions. You are right in
your post about it returning no data.

The table I made is table1. It is as follows:
Field1 contains the deceased last name, first name.
Field 2 contains the Date of Death.
Field3 contains the Caregiver's First name, Last Name.
Field4 contains the Caregiver's Address, Street, City, State, Zip.
This table would have about 135 individual records.
Each month we would run this program to extract records based on current
processing date.
For instance, if this were the 4th of November, 2008 we would want all
people who died in October, 2008 to print out(1 month from date of death).
Also, we would like to move the Caregiver's name and address to move to a
stored form letter in this manner:
John Smith
4628 Ashley View Lane
Charleston, SC 29405

Dear John Smith,
It's been about a month since your love one died........

Then, we would process the data records to extract those who have been dead
3 months(August 2008), then 6 months, then 9 months and finally, 12 months.
We would need a storage area for the 5 different form letters and a way to
move the Caregiver's name and address to the appropriate letter as above.

It was suggest in one post to change the query so as to include the 1 or 3
or 6 or 9 or 12 depending upon the run.
We may need separate fields for each element of the address.
Hope this posts OK.
Thanks to everyone for their patience and help.
--
David

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks

If you want to display a query here, please open it in design view; select
View... SQL from the menu; and post the SQL text here. It may look cryptic but
it's the *real* query, for which the grid is just a design tool, and the folks
who answer questions here can read it like today's newspaper.

As written - if I'm interpreting it correctly - this query will return an
error because the expression Dateadd("m",1)date of death)) makes no sense.
Even if it were corrected to Dateadd("m",1, [date of death]) it would return
only those records where the Date of Death was December 31, 1899 (one day
after the "anchor point" for Date/TIme fields), or was equal to one month
after the date of death - so you'll get no results.

Perhaps you could explain in words what results you want from the query.