I'm trying to create a query that will pull a distinct instance of a transaction from my 'many' table that matches up with my 'one' table by a unique identifier - and I keep getting stuck.
First table (my 'one'): tblUndup - MR_No, Acct, FirstAdmit, County Second table (the 'many'): tblTransactions - UnitMRNum, Account_Num, Service_Date What I'm trying to do: SELECT DISTINCT Service_Date FROM tblTransations WHERE MR_No = UnitMRNum The purpose is to perform a count of all MR numbers broken down by county and only on their first service date. Example output: MR: E00001234 County: Athens Service_Date: 7-24-04 <- This being the first ever transaction in the Transactions table. MR: E00003254 County: Meigs Service_Date: 7-24-04 Total Count for Athens County: 24 Total Count for Meigs County: 5 Thing is, because of the Transactions table, each MR_No has multiple transactions, sometimes more than one on the same date. What I need to be able to do, is simply count each MR_No once on the first service date recorded in the Transactions table. Judith -- 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:1734 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
