Can I do this in Access? Please help

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" wrote in message
...
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?
--
David


"John... Visio MVP" wrote:

"Steve" wrote in message
news
David,

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

Steve


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

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.
--
David


"caseysmydog" wrote:

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.
--
David

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" wrote in message
...
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?
--
David


"John... Visio MVP" wrote:

"Steve" wrote in message
news
David,

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

Steve


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

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" wrote:

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" wrote in message
...
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?
--
David


"John... Visio MVP" wrote:

"Steve" wrote in message
news David,

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

Steve


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

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...

"caseysmydog" wrote in message
...
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" wrote:

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" wrote in message
...
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?
--
David


"John... Visio MVP" wrote:

"Steve" wrote in message
news David,

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

Steve


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

On Fri, 14 Nov 2008 08:48:13 -0800, caseysmydog
wrote:

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.
--

John W. Vinson [MVP]

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


"John W. Vinson" wrote:

On Fri, 14 Nov 2008 08:48:13 -0800, caseysmydog
wrote:

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.
--

John W. Vinson [MVP]

"mcnewsxp" wrote:

what ever he quotes i'll do for half
;^P
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/

On Fri, 14 Nov 2008 13:34:01 -0800, caseysmydog
wrote:

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.
--

John W. Vinson [MVP]

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


"John W. Vinson" wrote:

On Fri, 14 Nov 2008 08:48:13 -0800, caseysmydog
wrote:

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.
--

John W. Vinson [MVP]