If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Can I do this in Access? Please help
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 |
#13
|
|||
|
|||
Can I do this in Access? Please help
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 |
#14
|
|||
|
|||
Can I do this in Access? Please help
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 |
#15
|
|||
|
|||
Can I do this in Access? Please help
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 |
#16
|
|||
|
|||
Can I do this in Access? Please help
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] |
#17
|
|||
|
|||
Can I do this in Access? Please help
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] |
#18
|
|||
|
|||
Can I do this in Access? Please help
"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/ |
#19
|
|||
|
|||
Can I do this in Access? Please help
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] |
#20
|
|||
|
|||
Can I do this in Access? Please help
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] |
Thread Tools | |
Display Modes | |
|
|