Chapter 4 Exercise 65

65. Based on the first seven letters of the trade name, what was the most commonly prescribed drug in July?

SELECT Left([TradeName],7) AS DrugName,
Count(VisitMedications.DrugSeqNo) AS CountOfDrugSeqNo
FROM Visit INNER JOIN (DrugListings INNER JOIN VisitMedications
ON DrugListings.SeqNo = VisitMedications.DrugSeqNo)
ON Visit.VisitID = VisitMedications.VisitID
WHERE (((Visit.VisitDate) Between #7/1/2013# And #7/31/2013#))
GROUP BY Left([TradeName],7)
ORDER BY Count(VisitMedications.DrugSeqNo) DESC;