Ok, I have another question, this time involving working with dates and date ranges.
Scenario: Table with Procedural Charges, some have two or more entries per charge depending on how often the costs have changed. Transactions table with multiple entries per account (basically every transaction for an account). Both of these tables have dates (ChargeProcedures has "EffectiveDate"; Transactions has "Episode"). Quandry: What I would like to do is get my query to actually pull the most recent charge from the ChargeProcedures table based on the Episode date from my Transactions table. But, I would like it to pull the next latest charge if the Episode date in my Transactions table is older than the latest chagne in the EffectiveDate in ChargeProcedures. Example: Transactions.Episode 11/29/2003 ChargeProcedures table has an entry for 1/1/2002 and a newer entry for 1/1/2004 I need to pull the cost and charge for the 2002 effectivedate, not for the 2004. Problem I've been running into is that I can't seem to get just *one* result, but instead I'm getting one for *each effectivedate entry in the table*. Since this feeds another calculation, my final numbers are off by however many entries for a particular ChargeProcedure exists in the table. Let me koe if this isn't clear. any help with this will be greatly appreciated. Thanks, Judith ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:15:533 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
