reports help

[babysilvertooth]

I am looking for report to help with archiving project.
Need to shred old paper charts, but want to pull a list to help make things easier.
Need to find patients who have not been seen in the last 7 years or more; helpful to be able to pull list of archived, inacive, and active patietns as some may not have been labled properly if they were previously shredded.

Thanks'

[Tom Zaccaria]

Does this help

/*130*/ SELECT patient.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', COUNT(procedurelog.ProcNum) AS '# Procs Total'
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcStatus=2
AND patient.PatStatus=0
GROUP BY procedurelog.PatNum
HAVING MAX(ProcDate)<'2011-01-15'
ORDER BY patient.LName, patient.FName

drtmz

[babysilvertooth]

Thanks, kinda helps, but it only came up with a handful of patients.....Hard to figure if that is accurate....I can't imagien over the last 30 years that only a handful are not 'active'.....
How about a query that shows all patients with last date...then I can sort in excell by last visit date.....Oh and an age would help too for people who were under 18, and I have to hold on to records....

[Tom Zaccaria]

Try this. It should get everyone,

/*130 modified*/
SELECT patient.PatNum, patient.birthdate,
DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit',
COUNT(procedurelog.ProcNum) AS '# Procs Total'
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcStatus=2
AND patient.PatStatus=0
GROUP BY procedurelog.PatNum
ORDER BY patient.LName, patient.FName