This query will list all of the medications delivered over a given date range by a particular DEA schedule. The example lists all schedule II medications delivered last year (2011 in this example). For other schedules, just change the roman numeral to the schedule you want.


SELECT DISTINCT anestheticrecord.AnestheticDate as 'Date', patient.PatNum as 'Patient #',patient.LName as 'Last Name',patient.FName as 'First Name',
patient.Gender,DATE_FORMAT(patient.Birthdate,'%m/%d/%Y') as 'DOB',patient.Address,patient.Address2 as 'Addr2',patient.City,patient.State,patient.Zip,
anesthmedsgiven.AnesthMedName as 'Anesthetic Med',anesthmedsgiven.QtyGiven as 'Qty Given',
anesthmedsgiven.QtyWasted as 'Qty Wasted',anesthmedsinventory.DEASchedule as 'DEA Schedule'
FROM anestheticrecord
LEFT JOIN anesthmedsgiven ON anesthmedsgiven.AnestheticRecordNum = anestheticrecord.AnestheticRecordNum
LEFT JOIN patient ON patient.PatNum = anestheticrecord.PatNum
LEFT JOIN anesthmedsinventory ON anesthmedsinventory.DEASchedule = (SELECT DEASchedule from anesthmedsinventory WHERE AnesthMedName = anesthmedsgiven.AnesthMedName)
WHERE DATE_FORMAT(anestheticdate,'%Y-%m-%d') >= '2011-01-01' AND
DATE_FORMAT(anestheticdate,'%Y-%m-%d') <= '2011-12-31' AND anesthmedsinventory.DEASchedule = 'II'
ORDER BY anestheticdate ASC

If you entered your DEA schedule numbers differently (eg., '2' instead of 'II' for schedule II, then use that character instead). If you want it to print in reverse order, i.e., latest records first, just change the 'ASC' at the end to 'DESC'

The information is always present in the database, so no.

However, if you wanted to keep a 'printed' copy that gets backed up with your normal backups, you would run one query for schedule II and one for all schedules (it's posted earlier on the forum), then save it to C:\OpenDentImages\D folder ('D' for DEA Schedules, or maybe make a subfolder there called DEA Schedules) as a plain text file or use CutePDF to save it there as a PDF.