Account labels?

[mikebarrdds]

Hi guys,

I want to print sheets of address labels (30 labels / sheet Avery form) of all "active" accounts... let's say accounts with patients who have been to the office in the last 2 years.

This is for a marketing newsletter (snail mail).

I found how to run labels for families, so that's good. It has a check box for "active" accounts. But, I'd like to narrow it down to "active" within a specific (recent) time period. Is that possible?

Please help. I want to get this out ASAP.

[Arna]

Your best bet is to actually use a query, export the data to excel and create a mail merge. Our Mailing options on the query example page are useful: http://opendentalsoft.com:1942/ODQueryL ... yList.aspx

Code: Select all

/*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 because it is the guarantors name, not the patient's name. */

/* This query gives names and addresses of guarantors for each family where at least one active patient has been seen (with procedures completed) within the past three years, and where the guarantor address has a valid zip code. Only visits after today's date three years ago are included. */

SET @FromDate = CURDATE() - INTERVAL 3 YEAR; 
SET @ToDate   = CURDATE();
SELECT g.LName,g.FName, g.Address, g.Address2, g.City, g.State, g.Zip
FROM patient p
INNER JOIN patient g ON p.Guarantor=g.PatNum
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE p.PatStatus=0
/*only patients with procedures completed within the last three years.*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2
AND Length(g.Zip)>4
GROUP BY g.PatNum
ORDER BY g.LName,g.FName

This will return a list of guarantors for families seen in the last three years. Export the data to an excel file and create a mail merge in Office: https://support.office.com/en-ca/articl ... c3a4b011b2

[ki0ak]

I'm in the same process and have utilized the SQL indicated, but on spot checking, it appears to be missing some patients' appointments. (A couple of patients seen in 2014 were not in the produced list.) Any ideas/suggestions? Thanks!

/*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 because it is the guarantors name, not the patient's name. */

/* This query gives names and addresses of guarantors for each family where at least one active patient has been seen (with procedures completed) within the past three years, and where the guarantor address has a valid zip code. Only visits after today's date three years ago are included. */

SET @FromDate = CURDATE() - INTERVAL 3 YEAR;
SET @ToDate = CURDATE();
SELECT g.LName,g.FName, g.Address, g.Address2, g.City, g.State, g.Zip
FROM patient p
INNER JOIN patient g ON p.Guarantor=g.PatNum
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE p.PatStatus=0
/*only patients with procedures completed within the last three years.*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2
AND Length(g.Zip)>4
GROUP BY g.PatNum
ORDER BY g.LName,g.FName

[ki0ak]

Never mind - figured it out. Those patients had been set to "Inactive" hence they weren't showing in the list.

Sorry for the confusion.

Brad