Note that it is far better practice (however, some would say confusing) to use INNER JOIN syntax rather than the FROM tblHOUndup, tblHOTransactions. It means exactly the same thing but it is better supported. The "," delimited way is not the norm anymore and not standard.
-----Original Message----- From: Ms. Judith Taylor [mailto:[EMAIL PROTECTED] Sent: 27 March 2006 19:33 To: CF-Newbie Subject: Re: One to Many query - RESOLVED Thank you Greg. Your response helped me figure out exactly what I was trying to do. What I ended up with is the following: <cfquery name="svcdates" datasource="PPSData"> SELECT DISTINCT UnitMRNum, MIN(Service_Date) AS FirstDate, County FROM tblHOUndup, tblHOTransactions WHERE UnitMRNum = MR_No GROUP BY UnitMRNum, County </cfquery> This gave me the first instance of UnitMRNum with the first instance of service date. I was then able to create a crosstab query that gave me the count of all UnitMRNum by county and month. -- Ms. Judith Taylor Appalachian Community Visiting Nurse Assoc., Hospice and Health Services, Inc. 740.594.8226 http://www.acvna.org ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1740 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
