Im just wondering if there is a specific SQL query that can be used to create a mailing list that can be used to send letters to each household rather then every patient registered from the same house. I wanted to export this into excel and mail merge a letter to each house / family
Thanks
Household mailing list
-
- Posts: 172
- Joined: Mon Aug 04, 2008 12:39 pm
Re: Household mailing list
Yes, one example is #35 available at http://www.open-dent.com/manual/queryexamples.html
35. List of families seen in the last three years. Useful for generating a list of patients for Christmas cards. After saving the resulting datafile, you would use the letter merge feature of Word to actually print the cards or labels. The first name is not very useful, since it might be anyone in the family. You would probably address it as <<LName>> Household, or something similar.
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 3 YEAR)
GROUP BY Guarantor
ORDER BY LName,FName
35. List of families seen in the last three years. Useful for generating a list of patients for Christmas cards. After saving the resulting datafile, you would use the letter merge feature of Word to actually print the cards or labels. The first name is not very useful, since it might be anyone in the family. You would probably address it as <<LName>> Household, or something similar.
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 3 YEAR)
GROUP BY Guarantor
ORDER BY LName,FName